Some XML-formatted data is sometimes processed in a stored procedure, so sp_xml_preparedocument is used, he can read the XML data, and then analyze it using the MSXML parser (Msxmlsql.dll). We can easily get the data we want in the XML in the stored procedure. The following code reads XML using sp_xml_preparedocument:
Copy Code code 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= "one" quantity= "/>"
<orderdetail orderid= "10248" productid= "quantity=" "/>"
</Order>
</Customer>
<customer customerid= "Lilas" contactname= "Carlos Gonzlez" >
<order customerid= "Lilas" employeeid= "3" orderdate= "1996-08-16t00:00:00" >
<orderdetail orderid= "10283" productid= "a" quantity= "3"/>
</Order>
</Customer>
</ROOT> '
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
Just read the XML, you need to use OPENXML to get the XML data, the code is as follows:
Copy Code code as follows:
SELECT *
From OpenXML (@hdoc, '/root/customer ', 1)
With (CustomerID VARCHAR), ContactName VARCHAR (40))
OPENXML has three parameters:
The first is the output parameter of sp_xml_preparedocument reading, which is @hdoc in this example;
The second is an XPath expression used to get the data at the specified location;
The third is an optional, used to indicate the way to get, there are 0,1,2,8 four kinds of values, detailed explanation please see
The from behind with is also optional, specifying which data fields to get, with the CustomerID and ContactName only taken from the code above. The results of the above query are as follows:
CustomerID ContactName
—————————————- —————————————-
Vinet Paul Henriot
Lilas Carlos Gonzlez
If you do not specify a with clause, the query comes out with a default table structure, as follows:
Explanatory notes for table columns:
| Column Name |
Data Type |
Description |
| Id |
bigint |
Unique ID of the document node. The ID value of the root element is 0. Retains the negative ID value. |
| ParentID |
bigint |
Identifies the parent node of the node. The parent node identified by this ID is not necessarily a parent element. The situation depends on the node type of the child node of the node identified by this ID. For example, if a node is a text node, its parent node might be an attribute node. If the node is at the top level of the XML document, its parentid is NULL. |
| Node type |
Int |
Identifies the node type, which is an integer corresponding to the XML object Model (DOM) node type number. 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 partial nodes 5 = entity reference node 6 = entity node 7 = processing instruction Node 8 = annotation node 9 = document node Ten = Document type node one = document fragment node A = notation node For more information, see the "Node Type Properties" topic in the Microsoft XML (MSXML) SDK. |
| LocalName |
nvarchar (max) |
Provides the local name of an element or attribute. Null if the DOM object does not have a name. |
| Prefix |
nvarchar (max) |
The namespace prefix for 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 NULL. Data types are inferred from inline DTDs or from inline schemas. |
| Prev |
bigint |
The XML ID of the previous sibling element. Null if there is no previous sibling element. |
| Text |
ntext |
Contains the property value or element content in the form of text. NULL If the edge table entry does not require a value. |
In the WITH clause, we can also get the attribute values of the parent element by setting:
Copy Code code 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= "One" quantity= "one"/>
<orderdetail productid= "A" quantity= "ten"/>
</Order>
</Customer>
<customer customerid= "Lilas" contactname= "Carlos Gonzlez" >
<order orderid= "10283" customerid= "Lilas" employeeid= "3"
Orderdate= "1996-08-16t00:00:00" >
<orderdetail productid= "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 results of the query are:
OrderID CustomerID OrderDate ProdID Qty
———– ———- ———————– ———– ———–
10248 vinet 1996-07-04 00:00:00.000 11 12
10248 vinet 1996-07-04 00:00:00.000 42 10
10283 Lilas 1996-08-16 00:00:00.000 72 3
Sometimes the data in the XML does not exist as a property, but instead is placed directly in the node as follows:
Copy Code code 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 get the values of the items under the Order node at this point, you can use the following method:
Copy Code code 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 ' CustomerID ',
EmployeeID int ' EmployeeID ',
OrderDate datetime ' OrderDate ')
The results of the query are as follows:
OrderID CustomerID EmployeeID OrderDate
———– ———- ———– ———————–
10248 vinet 5 1996-07-04 00:00:00.000
You can see whether it's the value of the text that takes the attribute value or the node, except that the third parameter of the WITH clause has the @ symbol
