Importing an XML Document Created by Microsoft Access

This example imports the following XML document, which was created from a Microsoft Access database. Because the XML document contains an embedded XML schema, you must specify the MSACCESS markup type rather than the default GENERIC type. MSACCESS obtains a variable's attributes from the embedded schema.

<?xml version="1.0" encoding="windows-1252" ?>
<root      xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:od="urn:schemas-microsoft-com:officedata">
   <xs:schema>
      <xs:element name="dataroot">
         <xs:complexType>
            <xs:sequence>
               <xs:element ref="SUPPLIERS"  minOccurs="0"
maxOccurs="unbounded" />
            </xs:sequence>
         </xs:complexType>
      </xs:element>
      <xs:element name="SUPPLIERS">
         <xs:complexType>
            <xs:sequence>
               <xs:element name="HOMEPAGE" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="94" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="FAX" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="15" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="PHONE" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="15" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="COUNTRY" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="11" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="POSTALCODE" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="8" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="REGION" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="8" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="CITY" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="13" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="ADDRESS" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="45" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="CONTACTTITLE" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="28" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="CONTACTNAME" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="26" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="COMPANYNAME" minOccurs="0"
                           od:jetType="text" od:sqlSType="nvarchar">
                  <xs:simpleType>
                     <xs:restriction base="xs:string">
                        <xs:maxLength value="38" />
                     </xs:restriction>
                  </xs:simpleType>
               </xs:element>
               <xs:element name="SUPPLIERID" minOccurs="0"
                           od:jetType="double" od:sqlSType="double"
type="xs:double" />
            </xs:sequence>
         </xs:complexType>
      </xs:element>
   </xs:schema>
   <dataroot>
      <SUPPLIERS>
         <HOMEPAGE/>
         <FAX/>
         <PHONE>(272) 444-2222</PHONE>
         <COUNTRY>UK</COUNTRY>
         <POSTALCODE>EC1 4SD</POSTALCODE>
         <REGION/>
         <CITY>London</CITY>
         <ADDRESS>49 Franklin St.</ADDRESS>
         <CONTACTTITLE>Purchasing Manager</CONTACTTITLE>
         <CONTACTNAME>Charlotte Smith</CONTACTNAME>
         <COMPANYNAME>Exotic Flowers</COMPANYNAME>
         <SUPPLIERID>1</SUPPLIERID>
      </SUPPLIERS>
      <SUPPLIERS>
         <HOMEPAGE>#MYCAJUN.HTM#</HOMEPAGE>
         <FAX/>
         <PHONE>(512) 284-3677</PHONE>
         <COUNTRY>USA</COUNTRY>
         <POSTALCODE>70117</POSTALCODE>
         <REGION>LA</REGION>
         <CITY>New Orleans</CITY>
         <ADDRESS>P.O. Box 78934</ADDRESS>
         <CONTACTTITLE>Order Administrator</CONTACTTITLE>
         <CONTACTNAME>Shelley Martin</CONTACTNAME>
         <COMPANYNAME>New Orleans Cajun Foods</COMPANYNAME>
         <SUPPLIERID>2</SUPPLIERID>
      </SUPPLIERS>
   .
   .
   .
   </dataroot>
</root>

The following SAS program interprets the XML document as a SAS data set:

libname access xml '/u/myid/XML/suppliers.xml' xmltype=msaccess 1
   xmlmeta=schemadata; 1

proc print data=access.suppliers (obs=2); 2
   var contactname companyname;
run;

The LIBNAME statement assigns the libref ACCESS to the physical location of the XML document (complete pathname, filename, and file extension) and specifies the XML engine. By default, the XML engine expects GENERIC markup, so you must include the XMLTYPE= option in order to read the XML document in MSACCESS markup and to obtain a variable's attributes from the embedded schema. The option XMLMETA=SCHEMADATA specifies to import both data and metadata-related information from the input XML document.

The PRINT procedure produces the output. The procedure uses the OBS= data set option to print only the first two observations, and the VAR statement to print only specific variables (columns).

PRINT Procedure Output for ACCESS.SUPPLIERS

Using the CONTENTS procedure, the output displays the file's attributes, as well as the attributes of each interpreted column (variable), such as the variable's type and length, which are obtained from the embedded XML schema. Without the embedded XML schema, the results for the attributes would be default values.

proc contents data=access.suppliers;
run;

CONTENTS Procedure Output for ACCESS.SUPPLIERS