The mapping schema in Listing 2 defines an XML View for the same document that Listing 1's FOR XML EXPLICIT query returns. The code uses the relation annotation to map the LineItem element to the Products table (you namespace-qualify all annotations by using the SQL prefix). Each row in the Products table produces a LineItem element in the XML View. The code also includes the overflow-field annotation on the definition of the LineItem element to signal that SQL Server should include the open content from the Overflow column when constructing the LineItem element. As you can see, including the open content is fairly simple.
Now, let's run the example code. Start by saving the mapping schema from Listing 2 to a file on your computer named products_map.xml. Also save the VBScript code from Listing 3 to a file called opencontent.vbs in the same directory where you saved the mapping schema. You might need to edit the script to adjust the connection settings for your environment. Now, open a command prompt in that directory and execute opencontent.vbs. In a pop-up window, the script code will display the XML it retrieved from your database. You might need to install Windows Script to execute opencontent.vbs. You can download Windows Script from http://msdn.microsoft.com/scripting.
Limitations
You need to be conscious of two limitations when including open content in XML query results: element order and naming conflicts. When SQL Server merges the attributes and elements in the open content with the elements and attributes created by the query, it doesn't preserve the relative ordering of the elements or attributes. All elements from the open content precede query-generated elements regardless of the columns' order in the query.
SQL Server appends attributes from the open content to the query-generated attribute list. But because attributes are, by definition, unordered, their relative order is insignificant. You can see the results of these limitations in Figure 2. Notice that the QuantityPerUnit element from the open content precedes the ProductName and Price elements, which the query generated directly, and the weight attribute appears after the id attribute. If the order of the elements in your XML query result holds some significance—as it might if the schema you're following dictates a specific order—you should choose an alternative storage format for storing the data that preserves order. For example, you could directly store the XML representation in a text column.
The second limitation involves the element and attribute names in the open content. You might encounter naming conflicts between the open content and the attributes and elements that the query generates directly. When a conflict occurs, SQL Server uses a simple set of rules to generate the query result. When an attribute name is in conflict, SQL Server discards the conflicting attribute from the open content. Only the attribute the query generated directly will appear in the result.
SQL Server treats elements differently. It doesn't detect conflicting element names; instead, it simply adds elements from the open content to the query-generated elements. And because of the ordering behavior I described earlier, duplicate elements from the open content will precede the query-generated elements. This can cause parsing-validation errors resulting from the duplicate elements or unexpected runtime errors from code that has dependencies on the element order. The best way to avoid such errors is to ensure that you don't store duplicate data in columns of your database and in open content and that the query doesn't explicitly include duplicate data. These limitations apply to both FOR XML EXPLICIT queries and XML Views because XML Views retrieve data from SQL Server by executing a FOR XML EXPLICIT query.
Extensibility Through Open Content
By using the techniques I described in this and my previous column, you can store open content in your database and include that open content in XML query results. With these techniques and technologies, you can build resilient, extensible applications today that adapt well to the presence of open content in the data the application receives—and sends—tomorrow.
End of Article
Prev. page
1
[2]
next page -->
