SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
Occasional Learner
Posts: 1

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

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-comSmiley Surprisedffice:spreadsheet"

xmlnsSmiley Surprised="urn:schemas-microsoft-comSmiley SurprisedfficeSmiley Surprisedffice"

xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"

xmlns:ss="urn:schemas-microsoft-comSmiley Surprisedffice: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 ssSmiley FrustratedtyleID="s21"><Data ss:Type="String">Name</Data></Cell>

<Cell ssSmiley FrustratedtyleID="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

SAS Super FREQ
Posts: 8,820

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

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

Ask a Question
Discussion stats
  • 1 reply
  • 951 views
  • 0 likes
  • 2 in conversation