Difficult XML to parse

Question

I don't know how to parse this kind of XML. I would like to get a simillar format as importing to Excel as attached. Book2.xlsx

gzmorgan0 · Answer

Thanks, @Craige_Hales.

I added the join of Associative arrays instead of using 3 different arrays to fill out the table with simpler syntax, as a last minute edit.  Then did not test tested thoroughly.  Yes, bitten by a rookie mistake.

The updated script is attached.

blog_XML_Parse.jsl

Craige_Hales · Answer

Nice script! needs one tiny (important!) change

OnElement( "resultSet", 
	 Start Tag(
      set_aa = XML Attr();    //get all attributes as name value pairs
	 )
	),

Using the start tag rather than the end tag will get the resultSet data straightened out.

JMP 15 (later this year) has a new import XML feature that can do this without writing JSL.

gzmorgan0 · Answer

@edwardlyx ,

Your task provides an excellent example of the power of JMP XML parsing functions and JMP associative arrays.  If you are not familiar with these concepts, the script might leave you wondering how this works.  Associative Arrays are keyed lists, also known as a dictionary, often an ordered list of name-value pairs. The JMP implemenation of associative arrays includes very useful methods such as insert to combine (used in the script), as well as, intersect and other nice  features.

XML Attr() if unnamed returns all attributes in a tag as an associative array with name value pairs.

Attached are two files:

* the .txt file was created by copying and pasting the embedded xml code in your blog post to a text file [Sorry this site will not allow me to add the XML file even if I rename it as .txt; an error reports the contents does not match the file type.]
* the script that creates the table.

If this script does not work, please post your version of JMP and your OS.

Names Default to Here(1);
path = "C:	emp";
fid = "blog_question_parse.xml";
file_contents = Load Text File( path || fid );

//-- Create a display of the XML format
nw_xml = New Window( "Blog XML", Text Box( file_contents ) );
wait(5);
dt = New Table( word(1, fid,"."),
	New Column( "rack", character ),
	New Column( "drawer", character ),
	New Column( "lastResult", character ),
	New Column( "test", character ),
	New Column( "tag", character ),
	New Column( "time", character ),
	New Column( "elapsedTime", character ),
	New Column( "type", character ),
	New Column( "pinset", character ),
	New Column( "lotID", character ),
	New Column( "carrierId", character ),
	New Column( "carrier", character ),
	New Column( "position", character ),
	New Column( "lowlimit", character ),
	New Column( "highlimit", character ),
	New Column( "value", character ),
	New Column( "passfail", character ),
);  //end New Table
clist = dt << get column names("string");

//XML Attr() returns an Associative array of all attribute values in a tag
Parse XML( file_contents,
	OnElement( "results", 
	  Start Tag( 
         tag_aa = XML Attr();  //get all attributes as name value pairs
	    ) 
	),
	OnElement( "resultSet", 
	 End Tag(
      set_aa = XML Attr();    //get all attributes as name value pairs
	 )
	), 
	OnElement( "result", 
	  Start Tag( dt << Add Rows( 1 ); crow = nrow(dt) ), 
	  End Tag( 
	   row_aa = XML Attr();        //get all attributes as name value pairs 
	   row_aa << Insert(tag_aa);   //JMP Associative Arrays can be joined with Insert
	   row_aa << Insert(set_aa);   // do it again and now row_aa has all values for the columns
	   for(i=1, i<=nitems(clist), i++,
	   	 column(dt,clist[i])[crow] = row_aa[clist[i]]

);
	  )
	)
);  //end Parse XML

numcol = {"rack", "drawer", "lastResult", "elapsedTime", 
  "carrier", "position", "lowlimit", "highlimit",
"value", "passfail"};

for(i=1, i<=nitems(numcol), i++,
	column(dt,numcol[i]) << Data Type("Numeric");
);

blog_XML_Parse.jsl