How to Use sp_xml_preparedocument to process XML documents

Sometimes some XML data is processed in the stored procedure. Therefore, sp_xml_preparedocument is used to read the XML data and analyze it using the MSXML analyzer (Msxmlsql. dll. We can easily get the data we want in XML in the stored procedure. The following code reads XML using sp_xml_preparedocument:
Copy codeThe Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='
<ROOT>
<Customer CustomerID = "VINET" ContactName = "Paul Henriot">
<Order CustomerID = "VINET" EmployeeID = "5" OrderDate = "1996-07-04T00: 00: 00">
<OrderDetail OrderID = "10248" ProductID = "11" Quantity = "12"/>
<OrderDetail OrderID = "10248" ProductID = "42" Quantity = "10"/>
</Order>
</Customer>
<Customer CustomerID = "LILAS" ContactName = "Carlos Gonzlez">
<Order CustomerID = "LILAS" EmployeeID = "3" OrderDate = "1996-08-16T00: 00: 00">
<OrderDetail OrderID = "10283" ProductID = "72" Quantity = "3"/>
</Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @ hdoc OUTPUT, @ doc

The preceding Code only reads XML. To obtain XML data, you must use OPENXML. The Code is as follows:
Copy codeThe Code is as follows:
SELECT *
FROM openxml (@ hdoc, '/ROOT/customer', 1)
WITH (CustomerID VARCHAR (40), ContactName VARCHAR (40 ))

OPENXML has three parameters:
The first one is the OUTPUT parameter read by sp_xml_preparedocument. In this example, @ hdoc;
The second is an XPath expression used to obtain data at the specified position;
The third option is an option used to indicate the obtaining method. There are four values: 0, 1, 2, and 8. For more information, see
FROM is optional. It is used to specify which data fields to obtain. In the above Code, only CustomerID and ContactName are obtained. The preceding query result is as follows:
CustomerID ContactName
----------------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
If the WITH clause is not specified, a default table structure is displayed, as follows:

Description of table columns:

Column name	Data Type	Description
Id	Bigint	The unique ID of the document node. The ID of the root element is 0. Retain the negative ID value.
Parentid	Bigint	Identifies the parent node of a node. The parent node identified by this ID is not necessarily a parent element. The specific situation depends on the node type of the subnode of the node identified by this ID. For example, if a node is a text node, its parent node may be an attribute node. If the node is at the top of the XML documentParentIDIs NULL.
Node Type	Int	Identifies the node type, which is an integer corresponding to the node type Number of the XML Object Model (DOM. The following values are values that can be displayed in this column to indicate the node type: 1= Element Node 2= Attribute node 3= Text node 4= CDATA nodes 5= Entity reference node 6= Entity Node 7= Processing command node 8= Comment nodes 9= Document Node 10= Document Type Node 11= Document fragment Node 12= Representation Node For more information, see the "node type attributes" topic in the Microsoft XML (MSXML) SDK.
Localname	Nvarchar (max)	Provide the local name of the element or attribute. If the DOM object does not have a name, it is NULL.
Prefix	Nvarchar (max)	The namespace prefix of the node name.
Namespaceuri	Nvarchar (max)	The namespace URI of the node. If the value is NULL, The namespace does not exist.
Datatype	Nvarchar (max)	The actual data type of the element or attribute row. Otherwise, it is NULL. Data types are derived from an inline DTD or an inline schema.
Prev	Bigint	The xml id of the element of the same level. If there is no sibling element before, it is NULL.
Text	Ntext	Contains attribute values or element content in text format. If the edge table item does not need a value, it is NULL.

In the WITH clause, we can also get the attribute value of the parent element through settings:
Copy codeThe Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='
<ROOT>
<Customer CustomerID = "VINET" ContactName = "Paul Henriot">
<Order OrderID = "10248" CustomerID = "VINET" EmployeeID = "5"
OrderDate = "1996-07-04T00: 00: 00">
<OrderDetail ProductID = "11" Quantity = "12"/>
<OrderDetail ProductID = "42" Quantity = "10"/>
</Order>
</Customer>
<Customer CustomerID = "LILAS" ContactName = "Carlos Gonzlez">
<Order OrderID = "10283" CustomerID = "LILAS" EmployeeID = "3"
OrderDate = "1996-08-16T00: 00: 00">
<OrderDetail ProductID = "72" Quantity = "3"/>
</Order>
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @ hdoc OUTPUT, @ doc
SELECT *
From openxml (@ hdoc, '/ROOT/Customer/Order/OrderDetail', 2)
WITH (OrderID int '../@ OrderID ',
CustomerID varchar (10) '../@ CustomerID ',
OrderDate datetime '../@ OrderDate ',
ProdID int '@ ProductID ',
Qty int '@ Quantity ')

The query result is:
OrderID CustomerID OrderDate ProdID Qty
------------------------
10248 VINET 00:00:00. 000 11 12
10248 VINET 00:00:00. 000 42 10
10283 LILAS 00:00:00. 000 72 3
Sometimes the data in XML does not exist in attribute mode, but is directly placed in the node, as follows:
Copy codeThe Code is as follows:
DECLARE @ doc varchar (1000)
SET @ doc ='
<ROOT>
<Customer CustomerID = "VINET" ContactName = "Paul Henriot">
<Order>
<OrderID> 10248 </OrderID>
<CustomerID> VINET </CustomerID>
<EmployeeID> 5 </EmployeeID>
<OrderDate> 1996-07-04T00: 00: 00 </OrderDate>
</Order>
</Customer>
</ROOT>'

To obtain the values of each item under the Order node, use the following method:
Copy codeThe Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='
<ROOT>
<Customer CustomerID = "VINET" ContactName = "Paul Henriot">
<Order>
<OrderID> 10248 </OrderID>
<CustomerID> VINET </CustomerID>
<EmployeeID> 5 </EmployeeID>
<OrderDate> 1996-07-04T00: 00: 00 </OrderDate>
</Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @ hdoc OUTPUT, @ doc
SELECT *
From openxml (@ hdoc, '/ROOT/Customer/order', 1)
WITH (OrderID int 'orderid ',
CustomerID varchar (10) 'mermerid ',
EmployeeID int 'employeeid ',
OrderDate datetime 'orderdate ')

The query result is as follows:
OrderID CustomerID EmployeeID OrderDate
--------------------
10248 VINET 5 00:00:00. 000
It can be seen that the difference between the value of the get attribute or the value of the text of the node is whether the third parameter of the WITH clause has the @ symbol.