To convert an XML document to a row result set using the OPENXML function

FOR XML clauses that convert a row result set to an XML result set, you use the OPENXML function if you want to convert the XML document into a result set. OPENXML is available in SQL Server 2000, but this function is enhanced in SQL Server 2005.

The syntax for OPENXML is:

The results of the run are:

FirstName　　　　 MiddleName　　　　　LastName
----------------　----------------　　----------------
Gustavo　　　　　 NULL　　　　　　　　Achong
Catherine　　　　 R.　　　　　　　　　Abel

Swapping the third argument of the OPENXML function in code to 2 returns a 2-line null value, because 2 indicates that the query is element-centric and there are no other elements under the row node. In the same way, if you give an XML document with only elements and no attributes, you should use parameter 2 instead of 1. So if you want to query out some of the data in the element's attributes and part of the element's child elements then we can change that argument to 3. Query statement and return results see code:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

The first parameter idoc is a handle to an XML document that needs to be obtained by calling Sp_xml_preparedocument to create an internal form of an XML document. The parameter rowpattern is an XPath pattern that identifies the node to be processed. The third argument uses 1 to indicate that the query is attribute-centric, and 2 indicates that the query is element-centric. The last with clause identifies the field to return.

Use the OPENXML function to convert the XML document to a row result set code:

declare @mydoc xml
set @mydoc='
<Person>
　<row FirstName="Gustavo" LastName="Achong" />
　<row FirstName="Catherine" MiddleName="R." LastName="Abel" />
</Person>

'--Defining an XML document

declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc--Get the handle to the XML document

SELECT * FROM OPENXML(@docHandle,'/Person/row',1)--1表示以属性为中心
WITH (FirstName nvarchar(50),MiddleName nvarchar(50),LastName nvarchar(50))

declare @mydoc xml
set @mydoc='
<Products>
　<Product Category="Book">
　　<Name>Windows 2008</Name>
　　<Vendor>Vendor1</Vendor>
　</Product>
　<Product Category="Book">
　　<Name>SQL2008</Name>
　　<Vendor>Vendor2</Vendor>
　</Product>
</Products>'
declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc
SELECT * FROM OPENXML(@docHandle,'/Products/Product',3)
WITH (Category nvarchar(50),Name nvarchar(50),Vendor nvarchar(50))

In fact, the third parameter, regardless of any positive integer SQL Server 2005, is not an error, Microsoft's official MSDN only gives 0, 1, 2, 8 of these 4 numbers mean. In fact, SQL Server 2005 is internally based on the value of the binary bit of the third parameter to determine how the query is made. The last 2 digits are 00 or 01 (for example: 0, 1, 4, 5 and so on) is the attribute-centric query, the last 2 is 10 (for example: 2, 6, 10, etc.) is the element-centric query, and the last 2 digits is 11 (for example: 3, 7, etc.) means both query attributes and query elements.