XML-to-SQL Mapping

Question

We're looking for a way to parse large amounts of XML files that conform to a rigid schema (specifically this one). All we want to do is create 1-to-1 data mappings between several database tables and the XML file, so we can set up a trigger on the table where our web service software package inserts the file, then automatically parses it and inserts the data into the database as we have defined.

What're the best ways to accomplish this within the realm of Microsoft's SQL Server and similar?

Answer 1

You can use SQLXML Bulk load 4.0 to bulk load your XML file into the SQL Server tables.
SQLXML bulkload object uses the XML data file and Schema file.

SCHEMA (XSD or XML) file contains the mapping information between the XML node to database column name.

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "Connection String"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"

http://msdn.microsoft.com/en-us/library/ms171878.aspx

http://msdn.microsoft.com/en-us/library/ms171806.aspx

Following is the sample data file and schema file.

Data File

<ROOT>  
  <Order OrderID="11" CustomerID="ALFKI">
    <Product ProductID="11" ProductName="Chai" />
    <Product ProductID="22" ProductName="Chang" />
  </Order>
  <Order OrderID="22" CustomerID="ANATR">
     <Product ProductID="33" ProductName="Aniseed Syrup" />
    <Product ProductID="44" ProductName="Gumbo Mix" />
  </Order>
</ROOT>

Schema Definition

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="OrderOD"
          parent="Ord"
          parent-key="OrderID"
          child="OrderDetail"
          child-key="OrderID" />

    <sql:relationship name="ODProduct"
          parent="OrderDetail"
          parent-key="ProductID"
          child="Product"
          child-key="ProductID" 
          inverse="true"/>
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Order" sql:relation="Ord" 
                            sql:key-fields="OrderID" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="Product" sql:relation="Product" 
                     sql:key-fields="ProductID"
                     sql:relationship="OrderOD ODProduct">
          <xsd:complexType>
             <xsd:attribute name="ProductID" type="xsd:int" />
             <xsd:attribute name="ProductName" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="OrderID"   type="xsd:integer" /> 
        <xsd:attribute name="CustomerID"   type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Answer 2

You could consider using SSIS to create "ETL package" with XML data source(s). Then you execute the package. Inside the package you can specify schema, mappings etc. SSIS does have "For Each File In Directory" container etc..