Use the openxml function to convert an XML document to a row result set [ZT]

The for XML clause converts a row result set to an XML result set. to convert an XML document to a row result set, use the openxml function. Openxml is provided in SQL Server 2000, but is enhanced in SQL Server 2005.
Openxml Syntax:

Openxml (IDOC Int   [ In ] , Rowpattern Nvarchar   [ In ] , [ Flags byte [in ] ])
[ With (schemadeclaration | tablename) ]

The first parameter IDOC is the handle of the XML document, which must be obtained by calling sp_xml_preparedocument to create an internal table form of the XML document. The rowpattern parameter is an XPATH pattern used to identify the node to be processed. In the third parameter, 1 indicates that the query is Attribute-centric, and 2 indicates that the query is element-centric. Finally, the with clause identifies the fields to be returned.
Use the openxml function to convert the XML document toCode:

Declare   @ Mydoc XML
Set   @ Mydoc = '
<Person>
<Row firstname = "Gustavo" lastname = "achong"/>
<Row firstname = "Catherine" middlename = "R." lastname = "Abel"/>
</Person>
' -- Define XML document
Declare   @ Dochandle   Int
Exec Sp_xml_preparedocument @ Dochandle Output, @ Mydoc
-- Get the XML document handle
Select   *   From Openxml ( @ Dochandle , ' /Person/row ' , 1 ) -- 1 indicates Attribute-centric
With (Firstname Nvarchar ( 50 ), Middlename Nvarchar ( 50 ), Lastname Nvarchar ( 50 ))

The running result is:
Firstname middlename lastname
------------------------------------------------
Gustavo null achong
Catherine R. Abel
If the third parameter of the openxml function in the Code is changed to 2, two rows of null value will be returned, because 2 indicates that the query is element-centric, and no other elements under the row node. In the same way, if the given XML document has only elements but no attributes, you must use parameter 2 instead of 1. If you want to query a part of the data in the element attributes and a part in the element sub-element, you can replace this parameter with 3. For the query statements and returned results, see the code:

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, no error is reported for the third parameter and any positive integer SQL Server 2005. Microsoft official msdn only provides the meaning of the four numbers 0, 1, 2, and 8. In fact, SQL Server 2005 determines the query method based on the value in the binary bit of the third parameter. The last two digits are 00 or 01 (for example, 0, 1, 4, and 5). The query is based on the attribute and the last two digits are 10 (for example: 2, 6, 10, etc.) is an element-centric query, and the last two digits are 11 (for example, 3, 7, etc.) indicates that both the query attribute and the query element are required.