Editor's Note: Send your XML questions to Rich Rollman at xmlquestions@sqlmag.com.
I have an XML document generated from a Recordset through the ADO Save method. I want to use XML Bulk Load to upload the data in the XML document into another database, but the upload fails when the XML contains date fields. How can I use XML Bulk Load to load ADO-generated date fields?
Your upload fails because XML Bulk Load requires a mapping schema to correctly process date fields—output from ADO in ISO8601 format, which is the XML Data Reduced (XDR) representation. Because SQL Server 2000 can't convert dates from ISO8601 format into SQL Server's internal format, a mapping schema is required to instruct XML Bulk Load to convert the date format before loading the data into SQL Server.
Building a mapping schema is usually easy. However, the unique structure of the XML output from ADO's Save method can pose a particular challenge. Figure 1 shows a sample XML document that ADO generates when it persists a Recordset that holds the result of the query
SELECT Top 5 OrderID, OrderDate FROM Orders
which uses the Northwind database. The XML document contains an inline schema, namespace-qualified elements, and an unnecessary tag around the row elements that contain the data. The rs:data element in Figure 1 is unnecessary because it introduces a level of hierarchy in the XML representation when no hierarchical relationship exists in the data itself. The presence of those entities requires you to construct a set of mapping schemas that correctly specify how XML Bulk Load should load the data and convert the date field values. Let's look at the construction process.
Listing 1, page 44, shows the process's first step—building the top-level mapping schema that includes references to the other schemas. The top-level schema contains one declaration for the xml element, which is the root of the document that ADO generated. (Note that the XML Language Specification reserves the use of the string xml and any strings with the prefix xml—including mixed-case variants—for future use. However, XML parsers let you use xml because the specification doesn't dictate its usage as an error. In any case, you need to avoid using xml for names in your XML document.) The sql:is-constant annotation on the xml element declaration instructs XML Bulk Load that the root element doesn't map directly to the database so that XML Bulk Load ignores the xml element when processing the XML document.
The xml element contains two subelements: s:Schema and rs:data. The next step in building a set of mapping schemas is to account for these subelements. The s:Schema element—an inline XDR schema—defines the data types, constraints, and ADO-specific metadata for the row element and its attributes, which hold the data from the Recordset. Because XML Bulk Load attempts to load all data from the XML document into the database, you need to ensure that XML Bulk Load doesn't treat the inline schema as data. Moreover, because the current XML Bulk Load implementation doesn't support inline schemas, XML Bulk Load generates an error if an inline mapping schema is present. Fortunately, Microsoft anticipated the user's need to ignore parts of XML documents when using XML Bulk Load by providing the sql:mapped annotation in mapping schemas. When you specify sql:mapped with the value false on any element or attribute declaration in a mapping schema, XML Bulk Load doesn't attempt to store the associated data in the database.
In most cases, adding the sql:mapped annotation to the Schema element's declaration in the top-level mapping schema causes XML Bulk Load to ignore the Schema element. But in this case, ADO specifies the Schema element from a namespace different from the xml root element's namespace. (See xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' on the xml element in Listing 1.) The uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882 namespace is an old version of the Uniform Resource Identifier (URI) for the XDR namespace, which corresponds to the prefix s. Because the example uses mapping schemas in XML Schema Definition (XSD) format (XSD is the World Wide Web Consortium—W3C—standard schema specification language) and XSD schemas don't support the declaration of elements from different namespaces, you need a separate mapping schema to declare the Schema element. Listing 2 shows this mapping schema, called the Schema mapping schema because it defines the Schema element and its content. Because your goal is to instruct XML Bulk Load to ignore the Schema element and its contents, you again use the sql:mapped annotation on the Schema element's declaration.
Prev. page
[1]
2
next page 
