Publication date: 08/13/2020

Parse XML

JSL has several commands available to parse XML.

Parse XML( string, On Element( "tagname", Start Tag( expr ), End Tag( expr ) ) );

parses an XML expression using the On Element() expression for specified XML tags.

value = XML Attr( "attribute name" );

extracts the string value of an XML argument when evaluating a Parse XML() expression.

value = XML Text();

extracts the string text of the body of an XML tag when evaluating a Parse XML() expression.

Example of Parsing XML

Suppose that a Microsoft Excel file contains one row of data from Big Class.jmp. The file is saved as the valid XML document BigclassExcel.xml, shown here and also saved in the JMP Samples/Import Data folder.

<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Bigclass">
  <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="41" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">name</Data></Cell>
    <Cell><Data ss:Type="String">age</Data></Cell>
    <Cell><Data ss:Type="String">sex</Data></Cell>
    <Cell><Data ss:Type="String">height</Data></Cell>
    <Cell><Data ss:Type="String">weight</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">KATIE</Data></Cell>
    <Cell><Data ss:Type="Number">12</Data></Cell>
    <Cell><Data ss:Type="String">F</Data></Cell>
    <Cell><Data ss:Type="Number">59</Data></Cell>
    <Cell><Data ss:Type="Number">95</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

The following script reads BigclassExcel.xml and creates a JMP data table with the information in it. This script, named ParseXML.jsl, is in the JMP Samples/Scripts folder.

file contents = Load Text File( "$SAMPLE_IMPORT_DATA/BigclassExcel.xml" );
Parse XML( file contents,
	OnElement(
		"urn:schemas-microsoft-com:office:spreadsheet^Worksheet",
		StartTag(
			sheetname = XML Attr(
				"urn:schemas-microsoft-com:office:spreadsheet^Name",
				"Untitled"
			);
			dt = New Table( sheetname );
			row = 1;
			col = 1;
		)
	),
	OnElement(
		"urn:schemas-microsoft-com:office:spreadsheet^Row",
		StartTag(
			If( row > 1, 		// first row has column names
				dt << Add Rows( 1 )
			)
		),
		EndTag(
			row++;
			col = 1;
		)
	),
	OnElement( "urn:schemas-microsoft-com:office:spreadsheet^Cell", EndTag( col++ ) ),
	OnElement(
		"urn:schemas-microsoft-com:office:spreadsheet^Data",
		EndTag(
			data = XML Text( collapse );
			If( row == 1,
				New Column( data, Character( 10 ) ), // first row has column names
				Column( col )[row - 1] = data // and other rows have data
			);
		)
	)
 
);
Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).
.