How to Use sp_xml_preparedocument to process XML documents

Source: Internet
Author: User
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.

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.

Sometimes some XML data is processed in the stored procedure, so it is used. It can read the XML data and then 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:
The Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='












'
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:
The 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:

In the WITH clause, we can also get the attribute value of the parent element through settings:
The Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='



OrderDate = "1996-07-04T00: 00: 00">






OrderDate = "1996-08-16T00: 00: 00">



'

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:
The Code is as follows:
DECLARE @ doc varchar (1000)
SET @ doc ='



10248
VINET
5
1996-07-04T00: 00: 00


'

To obtain the values of each item under the Order node, use the following method:
The Code is as follows:
DECLARE @ hdoc int
DECLARE @ doc varchar (1000)
SET @ doc ='



10248
VINET
5
1996-07-04T00: 00: 00


'
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.