The mapping schema I show here represents all SQL Server columns as attributes in an XML document—a schema called attribute normal form. You can also use a schema in element normal form, which represents each field as a subelement of the named element. Attribute normal form looks like
<table attr1="val" attr2 = "val" attr3="val">
</table>
Element normal form looks like
<table>
<attr1>val</attr1>
<attr2>val</attr2>
<attr3>val</attr3>
</table>
The XML mapping schema not only represents, for query purposes, which fields in the database tables map to which XML elements and attributes, but it also defines the shape of the resulting XML document. By carefully creating your XPath query and mapping schema, you can use SQL Server to build XML documents suitable for B2B exchange, for example. Using the mapping schema from Listings 1 and 2, for example, the XPath query
/stores[@stor_id > "7500"]
produces the XML output
<root>
<stores stor_id="7896" stor_name="Fricative
Bookshop" />
<stores stor_id="8042" stor_name="Bookbeat">
<discounts discounttype="Customer Discount"
stor_id="8042" discount="5.00" />
</stores>
</root>
Note that the ISAPI template file adds the <root> tag.
In implementation, SQL Server 2000 databases don't directly support XPath queries. When you submit the above query (either through the ISAPI program on the Web server or directly through ADO), the SQLOLEDB provider intercepts the query and transforms it into a FOR XML EXPLICIT query. The FOR XML EXPLICIT query is a specialized form of UNION query that specifies a particular XML tree shape for the results. Note, however, that the FOR XML EXPLICIT query that the XPath query and mapping schema generate might not give you as much control over the UNION query as a custom-written FOR XML EXPLICIT statement. For example, an XPath-generated FOR XML EXPLICIT query can't use an ORDER BY clause.
Decomposing with OpenXML
As I noted earlier, T-SQL's OpenXML function decomposes XML hierarchies into relational structures. For example, suppose someone emails you an XML document that contains student registration information and you need to use that information to update nine SQL Server tables. You can use OpenXML to map the XML data to relational rows and columns. The mappings that OpenXML uses look like a hybrid of SQL's CREATE TABLE syntax and an XPath mapping schema. The following shows an example mapping of data in an XML document to relational rows and columns:
CustomerID varchar(10) '../@CustomerID',
ProdID int '@ProductID',
Qty int '@Quantity'
These tuples represent the name of the database table column, the column's data type, and an XPath expression that maps the column to one or more nodes in the XML document. You use this syntax in the OpenXML call's WITH section. But if the columns map the XML document subset directly to one table, you only need to specify the XPath row pattern and substitute the table name for the mapping syntax.
You can use this mapping with OpenXML to produce an output rowset, or you can use it as input to insert, update, or delete statements. To use the mapping with OpenXML, you must first use SQL Server 2000's sp_xml_preparedocument stored procedure to convert the XML document into an internal XML document object model representation suitable for decomposition. Sp_xml_preparedocument parses the XML document, adds some helpful annotations, and returns a document handle, which you use in the OpenXML statement. When you're finished with the internal document, system stored procedure sp_xml_removedocument frees the memory allocated for it. Listing 3 shows an example of using OpenXML to add a row to a database table from an XML document. In this example, because of the one-to-one relationship between attributes of the authors elements and the columns in the underlying SQL Server table, the WITH schema clause doesn't contain the column names, types, or XPath expressions that identify document locations.
Because you can use one XML document to represent data in one or more tables, you can use XML documents as input to stored procedures to avoid multiple round-trips to the database. You might want to use an XML document in a stored procedure, for example, to add a customer record, 1-to-n order records, and 1-to-n line items per order in a single round-trip to the database. Because the number of orders and line items varies, specifying each order and item column as a separate stored procedure parameter is unwieldy at best. Listing 4 shows a simplified example of how you can use OpenXML to insert into multiple tables from a single XML document. Although the example declares the document inline, you would typically pass the document into the stored procedure.
Coming Together
SQL Server 2000's XML Views and mapping schemas let you easily integrate XML into your relational database environment. T-SQL's new OpenXML feature lets you decompose an XML document into multiple rows in multiple SQL Server tables. And SQL Server 2000's ISAPI application doesn't have to perform the translation that the technology preview's ISAPI application does. Still, SQL Server 2000's XML implementation lacks some important functions found in the technology preview. Understanding the differences between the two XML implementations can help you select which best fits your needs until the merged XML functionality is available.
End of Article
Prev. page
1
[2]
next page -->
