An easier and more efficient approach to passing an XML document as a parameter is to use the ADO 2.6 Stream object's CommandStream property to pass a command to SQL Server through the SQL Server OLE DB driver. The Stream object contains the XML document, which OpenXML passes to the stored procedure and processes. Listing 1 contains a sample XML document to pass to the stored procedure. To keep the example simple, the XML document is very structured and straightforward. Having a more complex XML document would complicate the OpenXML code required to extract the data from the XML document.
Listing 2 contains the T-SQL code for the runOpenXML stored procedure. Note that the stored procedure declares an ntext variable, @xmlDoc, which contains the XML document's contents. The code then passes the XML document as a parameter to the sp_xml_preparedocument stored procedure. This stored procedure then parses the XML document and prepares it for OpenXML's use. The OpenXML statement stores the name and state from the XML document's author data into the Authors table. Listing 3 shows the appropriate schema for the Authors table.
Microsoft designed ADO's CommandStream property specifically to work with SQL Server 2000's XML support. CommandStream accepts an XML template, which is an XML document with embedded queries. In this example, the XML template contains the stored-procedure call that Listing 2 shows.
Using a template in this way is similar to how the SQL XML Internet Server API (ISAPI) DLL executes an XML template requested through Microsoft IIS. In fact, both of these methods use the same services from the SQL Server OLE DB provider. Listing 4 shows the Visual Basic (VB) code that dynamically constructs the XML template. This template executes the stored procedure.
The VB module declares three ADO Stream objects. StrmIn reads the XML document, strmCmd contains the XML template that the ADO CommandStream property receives, and strmOut stores the stored procedure's output (i.e., the XML document that the stored procedure returns).
After opening all the Stream objects, you use the LoadFromFile method on strmIn to load the XML document from disk. Then, strmCmd builds the template, which consists of one <sql:query ...> tag to execute the stored procedure. Note that strmCmd builds the template only as far as the inclusion of the XML document. Then, you copy the contents of the XML document from strmIn to strmCmd.
Note the use of CDATA at callout A in Listing 4. The CDATA section is necessary to avoid substituting character entities for the XML tags (e.g., substituting < for '<') in the document. After you copy the XML document into the Stream, you close the CDATA section and provide the end tag to complete the template. Finally, you execute the ADO Command object to process the template and obtain the results.
Although the ADO Stream example avoids explicitly allocating strings, it still buffers the XML document in memory within the ADO Stream. Although the ADO Stream can load data into SQL Server more efficiently than VB code can, be aware of the memory consumption on both the middle tier and the SQL Server machine when you use this approach. I recommend this solution only for small documents or when you need to load data from an XML document only infrequently. In these cases, the simplicity of this solution is ideal.
Using XML Schemas, how do you constrain the range of values that an attribute value can store?
XML Schemas let you describe the structure of an XML document, such as what elements are allowed in the document, how to nest the elements, and the attributes that are allowed on each element among a slew of other properties. You can also use XML Schemas to describe other meta-information about the elements' content and attributes in a document (e.g., data type, cardinality, sequence).
An XML Schema is an XML document that uses a specific vocabulary—a set of elements and attributes—to specify the vocabulary of another XML document. An XML Schema is an XML document that describes the structure and the data contained within an XML document.
Just as a relational schema describes your database structure (for example, tables, columns, and data types), an XML Schema describes an XML document's data structure (such as elements, sub-elements, attributes, and data types). And just as you can use CHECK constraints to limit the values that the columns in a table can contain, you can use the restrictions in XML Schemas to limit the values in XML elements and attributes.
To limit the range of attribute values, XML Schemas provide a user-defined data-type mechanism, which lets you define a data type that has a set of restrictions such as the maximum and minimum values for numeric types or the maximum length of a string value. XML Schemas provide different ways to specify a user-defined data type.
The example that Listing 5 shows uses an inline simpleType definition (which is contained directly within the attribute) to restrict the daysinyear attribute's value to 365 or 366. Note that the <restriction> tag specifies a base type of positive integers that the XML Schema further refines to have maximum and minimum values.
As XML Schemas become integrated with applications and start replacing the use of Document Type Definitions (DTDs), a thorough understanding of XML Schemas' capabilities and their usage within Web applications and SQL Server will be important. I look forward to answering your questions about XML Schemas in future editions of "Exploring XML."