Your next step is to construct the Schema element's content model specification (the elements that the Schema element can contain). If you needed data from the Schema element's subelements, you would need to define all the subelements so that you could create a mapping to the database. But because you need only ensure that XML Bulk Load ignores the Schema element and whatever it contains, you can take advantage of the any tag in XSD schemas. The any tag declares that the Schema element might contain any element from any namespace. After you build the Schema mapping schema, you reference it from the top-level mapping schema by using XSD's import instruction. Import lets you associate a schema with a particular namespace and include the declarations from that schema into the top-level schema. In this example, associating the Schema mapping schema from Listing 2 with the namespace uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882 lets XML Bulk Load successfully ignore the Schema element in the ADO output.
Handling the rs:data element is a bit different from working with s:Schema. Like the xml element in the top-level mapping schema, rs:data uses the sql:is-constant annotation with the value false to indicate that rs:data doesn't map directly to the database. Typically, using the sql:is-constant annotation would sufficiently instruct XML Bulk Load to ignore the rs:data element. But like the Schema element described in the previous paragraph, the rs:data element is from the urn:schemas-microsoft-com:rowset namespace, which is different from the namespace of the root element. Therefore, you need to build a separate mapping schema for the rs:data element. Listing 3 shows the rs:data element's mapping schema, which you can label the data mapping schema. The data element's declaration again specifies the sql:is-constant annotation to indicate that the rs:data element doesn't map directly to the database. The content model for rs:data specifies that an arbitrary number of row elements might be contained within the rs:data element. Then, the top-level mapping schema uses XSD's import instruction to reference the data-mapping schema.
The final step in constructing the set of mapping schemas is to build the row element's mapping schema. But like the s:Schema and rs:data elements, the row element is in a different namespace from its containing element (rs:data) and therefore requires a different mapping schema. Thankfully, this schema is the last one you need to load ADO data.
In the mapping schema that Listing 4 shows—called the row mapping schema—the row element contains a sql:relation annotation that maps row elements to the Orders table, which Listing 5 defines. You map the OrderID and OrderDate attributes to Orders table columns of the same name by using the sql:field annotation. Because the attribute names directly match the column names in the Orders table, the default mapping typically would perform the desired mapping. However, you use the sql:field annotation here to explicitly specify the mapping and to illustrate how the field would be specified if the names didn't directly correspond. The OrderDate attribute also contains the sql:datatype annotation to indicate that a data format conversion is required and to instruct XML Bulk Load to perform a conversion from the XSD dateTime format (ISO8601 standard) to the SQL Server 2000 datetime format. XML Bulk Load automatically performs the conversion upon execution. Finally, you use the XSD import instruction in the data-mapping schema to reference the row-mapping schema.
Using the set of mapping schemas you've constructed, you can load a persisted ADO Recordset in XML format by using XML Bulk Load. Listing 6 shows the VBScript code containing the logic that SQL Server requires to execute the XML Bulk Load. The code creates an XML Bulk Load object by using XML Bulk Load Version 2, included with XML for SQL Server 2000 Web Release 2 (available at http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/027/001/602/msdncompositedoc.xml). Then, the code builds a connection string to access the database. To run the code, you need to modify the connection string to specify the database on your local system and the access credentials for accessing the database. You can use the SQL script from Listing 5 to create the Orders table that the mapping schemas reference. After setting the location of an error log to which XML Bulk Load will write error messages, the script calls the Execute method, passing to the method the filename of the top-level mapping schema and the filename of the XML document containing the data to be loaded as parameters. By referencing the top-level mapping schema, named BLSchema.xsd, which uses import statements to import the other schemas you constructed, XML Bulk Load loads all schemas in the set. After the Execute method finishes, you can use Query Analyzer to verify that the data was loaded successfully and that proper conversion of OrderDate took place.
End of Article
Prev. page
1
[2]
next page -->
