Storing Unmapped Data
Passing the third parameter to the sp_xml_preparedocument stored procedure is a painless fix for using namespaces with OpenXML. You'll find that the solution for storing unmapped data is also a breeze. XML by nature and by name is extensible. This extensibility makes applications resilient to certain changes in the data format by letting additional elements and attributes appear within the XML without breaking the applications that consume the data. As you saw earlier, OpenXML lets you extract—or map—data from the elements and attributes in the XML document to the rows and columns of the rowset that OpenXML generates. However, some elements and attributes might not map to your database. What if you want to store this data in case you need to return it in a query later? OpenXML can capture, return, and store this unmapped data—or the complete XML representation of the data (which in this column's example is one book)—in a column of the resulting rowset. To retrieve the unmapped data, you specify the @mp:xmltext metaproperty as the XPath query for the column pattern of a column in the rowset that OpenXML produces. OpenXML automatically inserts the proper XML data into the column. The default mode is to store all the data for the XML fragment that OpenXML's row pattern specifies. Alternatively, you can specify that OpenXML will store only the unmapped data. By setting the third bit of the flag parameter to OpenXML (flag is a bitmap—a set of bits that controls OpenXML's execution behavior), you can specify that OpenXML will include only unmapped data in the column that uses the @mp:xmltext metaproperty. To see how OpenXML stores the unmapped data, let's return to the book example.
You used OpenXML in Listing 2's stored procedure to build a relational view containing the book's title and the author's title and name. Now suppose you also want to store other data associated with the book so that when someone queries your database for information about the book, you can return not only the title and author information but also the genre, description, or any other attributes or elements included in the XML. To accomplish this task, you simply include another column in the rowset that OpenXML generates, designating @mp:xmltext as the column pattern. Listing 4 shows the OpenXML statement, which returns a rowset that contains the XML representation of each book in the Alldata column. This column contains the book element—including all of its attributes and descendants. Figure 2 shows the statement's results, which you obtain by executing the T-SQL stored procedure in Listing 5. (This code is identical to the T-SQL code in Listing 3 except that it calls ViewUsingNamespaceXMLText instead of ViewUsingNamespace.) By using the @mp:xmltext metaproperty, you can obtain all the data for each book without specifically knowing the name—and namespace—of each individual attribute or element that the XML document contains.
I encourage you to experiment on your own with OpenXML's features, including the two features described here. When you receive an XML document that contains data you need to store in one of your databases, OpenXML provides the most flexibility for extracting that data. Add to this flexibility OpenXML's ability to use T-SQL to write business logic that validates the data and executes INSERT and UPDATE statements, and you've acquired a valuable tool in your quest to write Internet-enabled applications.