As XML applications mature and enter their second generation, application developers will leverage the built-in extensibility of XML more frequently, adding new data elements to existing XML formats that first-generation applications use. How well first-generation applications work with this additional data depends on whether the developer considered extensibility in the application's architecture. Luckily, SQL Server 2000 has built-in functionality to account for additional data, even when you don't know what or how much additional data you might need to receive or send from your application. Let's explore this functionality by way of an example.
Say you've implemented an application that receives product data—name and unit price—from one of your suppliers and stores it in your SQL Server database. When a customer places an order, your order-entry application uses the product data to send a message to your fulfillment vendor specifying the individual items in the order. You've implemented the application, and it's running flawlessly. But now your supplier wants to send you data that specifies the shipping weight and package sizes for each product. Your application won't use this data but must forward it to your fulfillment vendor for order processing. Will you need to rewrite your product data import application and your order-processing application to account for this additional data? Fortunately, the answer is no—if you've designed and implemented your application to account for the presence of additional data in the XML you receive and send.
Additional data that's included within an XML document is called open content. Open content encompasses any attribute or element in the XML document that either isn't specified in an XML Schema Definition (XSD) describing the document or, when no schema is used, is unexpected in its context. For example, in the scenario I described above, you and your supplier might have agreed on a schema for products that includes name and unit price, but the supplier sends shipping weight and package size as well. Although your application can't act directly on open content, you can store the open content for later use alongside the data your application uses directly. In this case, you could pass the shipping weight and package sizes to your fulfillment vendor without ever knowing the supplier had included new data.
SQL Server 2000's XML technology provides functionality that stores open content in your database and includes open content in XML query results. You can use OpenXML, Updategrams, and XML Bulk Load to store open content in the column of a table in your database. You can then include this open content in a result produced by executing a FOR XML EXPLICIT query or an XPath query against an XML View.
Open Content and OpenXML
OpenXML lets you use T-SQL code to extract data from an XML document. You can think of OpenXML as providing a relational view or rowset (similar to the OPENROWSET T-SQL function) of the XML document. Using OpenXML, you can query the data in the XML document as if it were in a table in your database.
Using OpenXML's @mp:xmltext meta-property, you can store open content in a column in your database. Meta-properties are special column selectors you use with OpenXML's WITH clause that provide access to nonphysical parts of an XML document. These parts include the open content; the node ID, which uniquely identifies each element and attribute within the document (see my February column, "OpenXML's @mp:id Meta-Property," InstantDoc ID 27473); and the previous sibling node ID. The @mp:xmltext meta-property automatically extracts open content from an XML document so you can store it in your database. Let's look at how to use OpenXML's @mp:xmltext meta-property.
Suppose that when you build your product data import application, you want to use OpenXML to store the product data you receive in the Products table that Listing 1 shows. Let's say you also want to store any open content you receive with the product data in a column named Overflow in the same table. First, you construct the OpenXML statement to extract the product data from the XML. Callout A in Listing 2 shows the product data. (I included the XML in the T-SQL code to simplify the example. In a real application, the XML would be passed as a parameter.) Callout B shows the INSERT statement that uses OpenXML to extract the product name and unit price from the XML document.