How to import XML file produced by ods tagsets.excelxp ?

[gkotler]

To import XML file which looks like:

<?xml version="1.0" ?>

<VEHICLES>

  <FORD>

   <ROW>

    <Model>Mustang</Model>

    <Year>1965</Year>

  </ROW>

..........

..........

</VEHICLES>

the following XLMMAP may be used:

<?xml version="1.0" ?>
<SXLEMAP version="1.2">
<TABLE name="FORD">
<TABLE-PATH syntax="xpath"> /VEHICLES/FORD/ROW </TABLE-PATH>

<COLUMN name="Model">
<DATATYPE> string </DATATYPE>
<LENGTH> 20 </LENGTH>
<TYPE> character </TYPE>
<PATH syntax="xpath"> /VEHICLES/FORD/ROW/Model </PATH>
</COLUMN>
<COLUMN name="Year">
<DATATYPE> string </DATATYPE>
<LENGTH> 4 </LENGTH>
<TYPE> character </TYPE>
<PATH syntax="xpath"> /VEHICLES/FORD/ROW/Year </PATH>
</COLUMN>
</TABLE>
</SXLEMAP>

But how can we import XML files produced by  ODS tagsets.excelxp

which has "<cell" tags

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<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">

<Styles>

<Style ss:ID="s21">

<Font ss:Bold="1"/>

</Style>

</Styles>

<Worksheet ss:Name="Sheet1">

<Table>

<Row>

<Cell ss:StyleID="s21"><Data ss:Type="String">Name</Data></Cell>

<Cell ss:StyleID="s21"><Data ss:Type="String">Age</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="String">Jack</Data></Cell>

<Cell><Data ss:Type="Number">9</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="String">Jill</Data></Cell>

<Cell><Data ss:Type="Number">7</Data></Cell>

</Row>

</Table>

</Worksheet>

</Workbook>

Thank you,

     Gregory Kotler,

        Boston

[Cynthia_sas]

Hi:

  ODS TAGSETS.EXCELXP creates output in Microsoft Office 2003 Spreadsheet Markup Language XML. I believe there's been a paper about doing a "round trip" from SAS to Excel and then using an XML map to get the XML back into SAS format. I've never tried this myself, but I believe the papers point you to a macro that you can download to do the round trip.They are older papers, so you might want to work with Tech Support or the paper author to see whether there have been updates since the paper was originally written.

http://www2.sas.com/proceedings/sugi30/136-30.pdf

http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf

cynthia