SQL Server 2000 and XML for SQL Server 2000 Web releases (SQLXML) provide three ways in which you can store XML data. XML Bulk Load and Updategrams, two client-side technologies, use annotated schemas to specify the mapping between the contents of an XML document and the tables in your database. OpenXML is a server-side technology that lets you define a relational view on an XML document. With OpenXML's relational view, you can use T-SQL code to query the data in the XML document and store the results in your SQL Server database.
Each of these three storage technologies is designed for a particular purpose. XML Bulk Load stores data from very large XML documents in SQL Server. Updategrams perform optimistic updates of SQL Server data. (Optimistic updates are updates without locks, in which the system checks to see whether another user has changed the data after it was originally read.) And OpenXML provides familiar relational access for XML data.
Of these three technologies, OpenXML is the most flexible because it provides a programming model (T-SQL) that you can use to write business rules or perform computational logic on the XML data before storing it in your SQL Server database. However, because OpenXML is a server-based technology, if you use it frequently or with large documents, it can degrade SQL Server's performance. But if you've adopted the Microsoft .NET Framework, you can work around these performance and scalability limitations by using ADO.NET's DataSet, which gives you a powerful technology—including a full programming model—for storing XML data in SQL Server.
DataSets, DataTables, and XML Mapping
Last month, in "XML Query Results in .NET" (InstantDoc ID 39160), I showed you an easy way to generate XML query results from SQL Server by using a DataSet. By providing a relational cache that you can use on client and middle-tier machines, the DataSet can load and manipulate data from a variety of sources, including SQL Server, other relational databases, and XML.
When you load a DataSet from an XML document, the DataSet must map the data that's stored in the hierarchical XML representation into the DataSet's relational representation. For example, if you have an XML document that contains a list of Order elements that has nested LineItem elements as children, that document would most commonly be mapped to Orders and LineItems DataTables in the relational representation. The mapping is similar in purpose to the way OpenXML uses XPath queries to construct a relational view on the XML document. But instead of using XPath specifications, DataSets have their own way of mapping data.
DataSets use XML Schema Definition (XSD) schemas to map data from an XML document into the DataSet's relational cache. DataSets give you two ways that you can specify a schema to map the XML data. First, you can reference an XSD schema that defines the elements, attributes, and relationships that are used in the XML document. Alternatively, you can infer the schema directly from the XML document's structure. In other words, the DataSet can build a schema by examining the structure and content of the XML document.
When you reference an XSD schema, the DataSet uses the elements and attributes that are defined in the schema along with the relationships that are defined between the elements to construct the DataTables, DataColumns, and DataRelationships in the relational cache that you use to store the mapped XML data. I refer to the structure, or schema, of the relational cache generically as the shape of the cache. When processing the schema, the DataSet applies a set of rules, similar to default mapping rules that Updategrams and XML Bulk Load use when no annotations are specified in their mapping schema, to create the tables that the DataSet uses to store the mapped XML data. You can summarize the DataSet's mapping rules as follows:
- Complex elements—those that contain other elements or attributes—are mapped to tables.
- Attributes and simple-valued subelements—elements that contain only data, not other elements or attributes—are mapped to columns.
- Data types are mapped from the XSD types to .NET types.
The useful ADO.NET DataSet documentation, "Generating DataSet Relational Structure from XML Schema (XSD)" at (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/_generating_dataset_relational_structure_from_xsd.asp), contains full details of the mapping rules. By referencing the schema of your choice, you can control the shape of the cache that the DataSet creates.
Prev. page
[1]
2
next page 
