Exporting XML Documents

Exporting an XML Document with Separate Metadata

This example exports an XML document from a SAS data set and specifies a separate file to contain metadata-related information.

Because this example illustrates using the options XMLMETA= and XMLSCHEMA=, which are available for the MSACCESS format type, the example uses a SAS data set that was created from a Microsoft Access database.

The following SAS program exports an XML document from the SAS data set MYFILES.SUPPLIERS:

libname input 'c:\My Documents\myfiles'; 

filename xsd 'c:\My Documents\XML\suppliers.xsd'; 
                                                             
libname output xml 'c:\My Documents\XML\suppliers.xml' xmltype=msaccess 
   xmlmeta=schemadata xmlschema=xsd'; 

data output.suppliers;                                                                                                                  
   set input.suppliers;                                                                                                               
run;

The first LIBNAME statement assigns the libref INPUT to the physical location of the SAS library that stores the SAS data set SUPPLIERS.
The FILENAME statement assigns the fileref XSD to the physical location of the separate external file that will contain the metadata-related information.
The second LIBNAME statement assigns the libref OUTPUT to the physical location of the file that will store the exported XML document (complete pathname, filename, and file extension) and specifies the XML engine. The engine options
- XMLTYPE=MSACCESS supports the markup standards for a Microsoft Access database.
- XMLMETA=SCHEMADATA specifies to include both data content and metadata-related information in the exported markup.
- XMLSCHEMA= specifies the fileref that is assigned, in the previous FILENAME statement, to the separate external file that will contain the metadata-related information.
The DATA step reads the SAS data set INPUT.SUPPLIERS and writes its data content in Microsoft Access database XML format to the XML document Suppliers.XML, then writes the metadata information to the separate external file Suppliers.XSD.

Here is part of the resulting XML document.

XML Document Suppliers.XML

<?xml version="1.0" encoding="windows-1252" ?>
   <dataroot  xmlns:xs="http://www.w3.org/2001/XMLSchema"
              xmlns:od="urn:schemas-microsoft-com:officedata">
              xs:noNamespaceSchemaLocation="SUPPLIERS.xsd">
      <SUPPLIERS>
         <SupplierID>1</SupplierID>
         <CompanyName>Exotic Flowers</CompanyName>
         <ContactName>Charlotte Smith</ContactName>
         <ContactTitle>Purchasing Manager</ContactTitle>
         <Address>49 Franklin St.</Address>
         <City>London</City>
         <Region/>
         <PostalCode>EC1 4SD</PostalCode>
         <Country>UK</Country>
         <Phone>(272) 444-2222</Phone>
         <Fax/>
         <HomePage/>
      </SUPPLIERS>
      <SUPPLIERS>
         <SupplierID>2</SupplierID>
         <CompanyName>New Orleans Cajun Foods</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-3677</Phone>
         <Fax/>
         <HomePage>#MYCAJUN.HTM#</HomePage>
      </SUPPLIERS>
      .
      .
      .
   </dataroot>

And here is the separate metadata information.

Separate Metadata Information Suppliers.XSD

<?xml version="1.0" encoding="windows-1252" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:od="urn:schemas-microsoft-com:officedata">
   <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="SupplierID" minOccurs="0"
             od:jetType="double" od:sqlSType="double" type="xs:double" />
            <xs:element name="CompanyName" minOccurs="0"
             od:jetType="text" od:sqlSType="nvarchar">
               <xs:simpleType>
                  <xs:restriction base="xs:string">
                     <xs:maxLength value="40" />
                  </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="30" />
                  </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="30" />
                  </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="60" />
                  </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="15" />
                  </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="15" />
                  </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="10" />
                  </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="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="24" />
                  </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="24" />
                  </xs:restriction>
               </xs:simpleType>
            </xs:element>
            <xs:element name="HomePage" minOccurs="0"
             od:jetType="text" od:sqlSType="nvarchar">
               <xs:simpleType>
                  <xs:restriction base="xs:string">
                     <xs:maxLength value="256" />
                  </xs:restriction>
               </xs:simpleType>
            </xs:element>
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>

Top of Page