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.
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
);
)
)
);