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

gkotler — Tue, 19 Feb 2013 21:15:37 GMT

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

</Style>

</Styles>

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

</Row>

<Row>

</Row>

</Table>

</Worksheet>

</Workbook>

Thank you,

Gregory Kotler,

Boston

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

Cynthia_sas — Wed, 20 Feb 2013 05:13:22 GMT

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

topic Re: How to import XML file produced by ods tagsets.excelxp ? in Microsoft Integration with SAS

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

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