Base SAS

Importing an XML Document Created by Microsoft Access

The XMLTYPE= option now supports the MSACCESS type. MSACCESS is the XML format for the markup standards supported for a Microsoft Access database.

When importing, MSACCESS supports Microsoft Access 2002 and 2003 databases. When exporting, use the TAGSET= option and specify TAGSETS.SASXMAC2002 for Microsoft Access 2002 or TAGSETS.SASXMAC2003 for Microsoft Access 2003.

For example, the following LIBNAME statement specifies MSACCESS for a Microsoft Access 2002 database:

   libname msa xml 'C:\Myfiles\access.xml' xmltype=msaccess
      tagset=tagsets.sasxmac2002;

MSACCESS differs from the GENERIC format type in the way the XML engine determines a variable's attributes (such as a variable's type and length):

GENERIC determines a variable's attributes from the data.
If there is an embedded XML schema, specifying MSACCESS and the XMLMETA=SCHEMADATA option causes a variable's attributes to be obtained from the embedded schema. If a schema is not embedded, MSACCESS uses default values for attributes.

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 use the MSACCESS format type rather than the default GENERIC format type.

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"
   xmlns:od="urn:schemas-microsoft-com:officedata">
<xsd:schema>
<xsd:element name="dataroot">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element ref="Suppliers"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="Suppliers">
<xsd:annotation>
<xsd:appinfo>
<od:index index-name="PrimaryKey" index-key="SupplierID "
   primary="yes" unique="yes" clustered="no"/>
<od:index index-name="CompanyName" index-key="CompanyName "
   primary="no" unique="no" clustered="no"/>
<od:index index-name="PostalCode" index-key="PostalCode "
   primary="no" unique="no" clustered="no"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="SupplierID" od:jetType="autonumber" od:sqlSType="int"
   od:autoUnique="yes" od:nonNullable="yes">
<xsd:simpleType>
<xsd:restriction base="xsd:integer"/>
</xsd:simpleType>
</xsd:element>
<xsd:element name="CompanyName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="40"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="ContactName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
   .
   .
   .
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<dataroot xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<Suppliers>
<SupplierID>1</SupplierID>
<CompanyName>Exotic Flowers</CompanyName>
<ContactName>Charlotte Smith</ContactName>
<ContactTitle>Purchasing Manager</ContactTitle>
<Address>49 Franklin St.</Address>
<City>London</City>
<PostalCode>EC1 4SD</PostalCode>
<Country>UK</Country>
<Phone>(272) 444-2222</Phone>
</Suppliers>
<Suppliers>
<SupplierID>2</SupplierID>
<CompanyName>New Orleans Cajun Delights</CompanyName>
<ContactName>Shelley Martin</ContactName>
<ContactTitle>Order Administrator</ContactTitle>
<Address>P.O. Box 78934</Address>
<City>New Orleans</City>
<Region>LA</Region>
<PostalCode>70117</PostalCode>
<Country>USA</Country>
<Phone>(512) 284-3277</Phone>
<HomePage>#MYCAJUN.HTM#</HomePage>
</Suppliers>
   .
   .
   .
</dataroot>
</root>

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

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

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

The LIBNAME statement assigns the libref ACCESS to the physical location (complete pathname, filename, and file extension) of the XML document. The LIBNAME statement also specifies the XML engine. By default, the XML engine expects GENERIC format, so you must include the XMLTYPE= option in order to read the XML document in MSACCESS format. 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 procedure uses the VAR statement to print only specific variables (columns).

PROC PRINT Output for ACCESS.SUPPLIERS

                            The SAS System                                    1

Obs    COMPANYNAME                               CONTACTNAME

  1    Exotic Flowers                            Charlotte Smith
  2    New Orleans Cajun Foods                   Shelley Martin

Using PROC CONTENTS to See Attributes

Using PROC CONTENTS, the output displays the file's attributes as well as the attributes of each interpreted variable, 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;

PROC CONTENTS Output for ACCESS.SUPPLIERS

                               The SAS System                                       2

                           The CONTENTS Procedure

      Data Set Name        ACCESS.SUPPLIERS    Observations          .
      Member Type          DATA                Variables             12
      Engine               XML                 Indexes               0
      Created              .                   Observation Length    0
      Last Modified        .                   Deleted Observations  0
      Protection                               Compressed            NO
      Data Set Type                            Sorted                NO
      Label
      Data Representation  Default
      Encoding             Default


                 Alphabetic List of Variables and Attributes

    #    Variable        Type    Len    Format    Informat    Label

    5    Address         Char     60    $60.      $60.        Address
    6    City            Char     15    $15.      $15.        City
    2    CompanyName     Char     40    $40.      $40.        CompanyName
    3    ContactName     Char     30    $30.      $30.        ContactName
    4    ContactTitle    Char     30    $30.      $30.        ContactTitle
    9    Country         Char     15    $15.      $15.        Country
   11    Fax             Char     24    $24.      $24.        Fax
   12    HomePage        Char    256    $256.     $256.       HomePage
   10    Phone           Char     24    $24.      $24.        Phone
    8    PostalCode      Char     10    $10.      $10.        PostalCode
    7    Region          Char     15    $15.      $15.        Region
    1    SupplierID      Num       8    F8.       F8.         SupplierID

FOCUS AREAS

Base SAS

Importing an XML Document Created by Microsoft Access

Using PROC CONTENTS to See Attributes