OPENXML Enhancements

The FOR XML clause is great for retrieving XML from the SQL Server 2005 database. The FOR XML clause essentially creates an XML document from relational data. The OPENXML keyword is the counterpart to the FOR XML clause. The OPENXML function provides a relational rowset over an XML document. To use SQL Server’s OPENXML functionality, you must first call the sp_xml_preparedocument stored procedure, which parses the XML document using the XML Document Object Model (DOM) and returns a handle to OPENXML. OPENXML then provides a rowset view of the parsed XML document. When you are finished working with the document, you then call the sp_xml_removedocument stored procedure to release the system resources consumed by OPENXML and the XML DOM. You can see an overview of this process in Figure 7-1.

Figure 7-1: An overview of OPENXML

With SQL Server 2005 the OPENXML support has been extended to include support for the new XML data type, and the new user-defined data type. The following example shows how you can use OPENXML in conjunction with a WITH clause in conjunction with the new XML data type:

DECLARE @hdocument int DECLARE @doc varchar(1000) SET @doc ='<MyXMLDoc>       <DocumentID>1</DocumentID>       <DocumentBody>"OPENXML Example"</DocumentBody>     </MyXMLDoc>' EXEC sp_xml_preparedocument @hdocument OUTPUT, @doc SELECT * FROM OPENXML (@hdocument, '/MyXMLDoc', 10)   WITH (DocumentID  varchar(4),         DocumentBody varchar(50)) EXEC sp_xml_removedocument @hdocument 

At the top of this listing you can see where two variables are declared. The @hdocument variable will be used to store the XML document handle returned by the sp_xml_preparedocument stored procedure, while the @doc variable will contain the sample XML document itself. Next, the sp_xml_preparedocument stored procedure is executed and passed the two variables. The sp_xml_preparedocument stored procedure uses XML DOM to parse the XML document and then returns a handle to the parsed document in the @hdocument variable. That document handle is then passed to the OPENXML keyword used in the SELECT statement.

The first parameter used by OPENXML is the document handle contained in the @hdocument variable. The second parameter is an XPath pattern that specifies the nodes in the XML document that will construct the relational rowset. The third parameter specifies the type of XML-to-relational mapping that will be performed. The value of 2 indicates that element-centric mapping will be used. A value of 1 would indicate that attribute-centric mapping would be performed. The WITH clause provides the format of the rowset that’s returned. In this example, the WITH clause specifies that the returned rowset will consist of two varchar columns named DocumentID and DocumentBody. While this example shows the rowset names matching the XML elements, that’s not a requirement. Finally, the sp_xml_removedocument stored procedure is executed to release the system resources.

This SELECT statement using the OPENXML feature will return a rowset that consists of the element values from the XML document. You can see the results of using OPENXML in the following listing:

DocumentID DocumentBody ---------- -------------------------------------------------- 1          "OPENXML Example" (1 row(s) affected)