Importing an XML Document Using the AUTOMAP= Option to Generate an XMLMap

This example illustrates how to import an XML document using theAUTOMAP= optionto automatically generate an XMLMap file. By specifying the AUTOMAP= option in the LIBNAME statement, SAS analyzes the structure of the specified XML document and generates XMLMap syntax that describes how to interpret the XML markup into a SAS data set or data sets, variables (columns), and observations (rows).

Here is the XML document NHL.XML to be imported. If you try to import the document without an XMLMap, an error indicates that the data is not in a supported format.

<?xml version="1.0" encoding="iso-8859-1" ?>
<NHL>
  <CONFERENCE> Eastern 
    <DIVISION> Southeast  
      <TEAM name="Thrashers"  abbrev="ATL" />  
      <TEAM name="Hurricanes" abbrev="CAR" />  
      <TEAM name="Panthers"   abbrev="FLA" />  
      <TEAM name="Lightning"  abbrev="TB" />  
      <TEAM name="Capitals"   abbrev="WSH" />  
   </DIVISION>
 </CONFERENCE> 

 <CONFERENCE> Western
   <DIVISION> Pacific  
     <TEAM name="Stars"   abbrev="DAL" />  
     <TEAM name="Kings"   abbrev="LA" />  
     <TEAM name="Ducks"   abbrev="ANA" />  
     <TEAM name="Coyotes" abbrev="PHX" />  
     <TEAM name="Sharks"  abbrev="SJ" />  
   </DIVISION>
  </CONFERENCE> 
</NHL>

The following SAS statements import the XML document NHL.XML:

filename NHL 'C:\My Documents\XML\NHL.xml'; 1
filename MAP 'C:\My Documents\XML\NHLGenerate.map'; 2

libname NHL xmlv2 automap=replace xmlmap=MAP; 3

proc print data=NHL.TEAM; 4
run;

The first FILENAME statement assigns the file reference NHL to the physical location (complete pathname, filename, and file extension) of the XML document named NHL.XML to be imported.
The second FILENAME statement assigns the file reference MAP to the physical location of the XMLMap named NHLGenerate.MAP to be generated.
The LIBNAME statement includes the following arguments:
- The LIBNAME statement assigns the library reference NHL, which matches the file reference that is assigned in the first FILENAME statement. Because the library reference and file reference match, the physical location of the XML document to be imported does not have to be specified in the LIBNAME statement.
- The XMLV2 engine is specified.
- The AUTOMAP=REPLACE option requests an XMLMap file to be generated and to overwrite the filename, if it exists.
- The XMLMAP= option specifies the file reference MAP, which matches the file reference that is assigned in the second FILENAME statement. The file reference is associated with the physical location of the XMLMap to be generated.
PROC PRINT produces output, verifying that the import was successful.

Here is the generated NHLGenerate.MAP XMLMap:

Generated NHL.Generate.MAP XMLMap

<?xml version="1.0" encoding="UTF-8"?>

<!-- ############################################################ -->
<!-- 2012-04-04T11:16:00 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 903200.2.0.20120301190000_v930m2 -->
<!-- ############################################################ -->
<!-- ###  Validation report                                   ### -->
<!-- ############################################################ -->
<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="2.1">

    <NAMESPACES count="0"/>

    <!-- ############################################################ -->
    <TABLE description="NHL" name="NHL">
        <TABLE-PATH syntax="XPath">/NHL</TABLE-PATH>

        <COLUMN class="ORDINAL" name="NHL_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

    </TABLE>

    <!-- ############################################################ -->
    <TABLE description="CONFERENCE" name="CONFERENCE">
        <TABLE-PATH syntax="XPath">/NHL/CONFERENCE</TABLE-PATH>

        <COLUMN class="ORDINAL" name="NHL_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN class="ORDINAL" name="CONFERENCE_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL/CONFERENCE</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN name="CONFERENCE">
            <PATH syntax="XPath">/NHL/CONFERENCE</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>7</LENGTH>
        </COLUMN>

    </TABLE>

    <!-- ############################################################ -->
    <TABLE description="DIVISION" name="DIVISION">
        <TABLE-PATH syntax="XPath">/NHL/CONFERENCE/DIVISION</TABLE-PATH>

        <COLUMN class="ORDINAL" name="CONFERENCE_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL/CONFERENCE</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN class="ORDINAL" name="DIVISION_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL/CONFERENCE/DIVISION</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN name="DIVISION">
            <PATH syntax="XPath">/NHL/CONFERENCE/DIVISION</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>9</LENGTH>
        </COLUMN>

    </TABLE>

    <!-- ############################################################ -->
    <TABLE description="TEAM" name="TEAM">
        <TABLE-PATH syntax="XPath">/NHL/CONFERENCE/DIVISION/TEAM</TABLE-PATH>

        <COLUMN class="ORDINAL" name="DIVISION_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL/CONFERENCE/DIVISION</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN class="ORDINAL" name="TEAM_ORDINAL">
            <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/NHL/CONFERENCE/DIVISION/TEAM</INCREMENT-PATH>
            <TYPE>numeric</TYPE>
            <DATATYPE>integer</DATATYPE>
        </COLUMN>

        <COLUMN name="name">
            <PATH syntax="XPath">/NHL/CONFERENCE/DIVISION/TEAM/@name</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>10</LENGTH>
        </COLUMN>

        <COLUMN name="abbrev">
            <PATH syntax="XPath">/NHL/CONFERENCE/DIVISION/TEAM/@abbrev</PATH>
            <TYPE>character</TYPE>
            <DATATYPE>string</DATATYPE>
            <LENGTH>3</LENGTH>
        </COLUMN>

    </TABLE>

</SXLEMAP>

Here is the PRINT procedure output for NHL.TEAM.

PRINT Procedure Output for NHL.TEAM