Shulou(Shulou.com)05/31 Report--

How to use sp_xml_preparedocument to deal with XML documents, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Sometimes some data in XML format is processed in the stored procedure, so sp_xml_preparedocument is used, and he 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 the XML using sp_xml_preparedocument: the copy code is as follows: DECLARE @ hdoc int DECLARE @ doc varchar (1000) SET @ doc =''EXEC sp_xml_preparedocument @ hdoc OUTPUT, @ doc

The above only reads the XML, and you need to use OPENXML to get the XML data as follows: copy the code as follows: SELECT * FROM openxml (@ hdoc,'/ROOT/Customer',1) WITH (CustomerID VARCHAR (40), ContactName VARCHAR (40))

OPENXML has three parameters: the first is the OUTPUT parameter that sp_xml_preparedocument reads yes. In this example, @ hdoc; is a XPath expression that is used to obtain the data at the specified location. The third is an optional one, which is used to represent the acquisition method. There are four values for 0Ling, one, two, and eight. For a detailed explanation, please see the WITH after FROM, which is used to specify which data fields to get. Only CustomerID and ContactName are taken in the above code. The above query result is as follows: CustomerID ContactName-- VINET Paul Henriot LILAS Carlos Gonzlez if you do not specify the WITH clause, the query results in a default table structure, as follows:

Explanation of the table column:

The unique ID of the idbigint document node.

The ID value of the root element is 0. Keep a negative ID value.

Parentidbigint identifies the parent of a node. The parent node of this ID identity is not necessarily a parent element. The situation depends on the node type of the child nodes of the node identified by this ID. For example, if the node is a text node, its parent node may be an attribute node.

If the node is at the top level of the XML document, its ParentID is NULL.

The node type int identifies the node type and 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 = comment node

9 = document node

10 = document type node

11 = document fragment node

12 = representation node

For more information, see the "Node Type Properties" topic in Microsoft XML (MSXML) SDK.

Localnamenvarchar (max) provides the local name of the element or attribute. NULL if the DOM object does not have a name. The namespace prefix of the prefixnvarchar (max) node name. Namespace URI of the namespaceurinvarchar (max) node. If the value is NULL, the namespace does not exist. The actual data type of the datatypenvarchar (max) element or property row, otherwise it is NULL. Data types are inferred from inline DTD or from inline schemas. The XML ID of the previous sibling element of prevbigint. NULL if there is no sibling element preceding it. Textntext contains attribute values or element content in textual form. NULL if the edge table item does not require a value.

Column name data type description

In the WITH clause, we can also get the attribute value of the parent element by setting: the copy code is as follows: DECLARE @ hdoc int DECLARE @ doc varchar (1000) SET @ doc =''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 result of the query is: OrderID CustomerID OrderDate ProdID Qty-10248 VINET 1996-07-04 00 VINET 00.000 11 12 10248 VINET 1996-07-04 00 VINET 00.000 42 10 10283 LILAS 1996-08-16 00 XML sometimes the data in the XML does not exist as attributes, but is directly placed in the node. The copy code is as follows: DECLARE @ doc varchar (1000) SET @ doc = '10248 VINET 5 1996-07-04T00:00:00'

To get the values of items under the Order node, you can use the following method: copy the code 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)' CustomerID', EmployeeID int 'EmployeeID', OrderDate datetime' OrderDate')

The query result is as follows: OrderID CustomerID EmployeeID OrderDate-10248 VINET 5 1996-07-04 00 WITH 00.000 you can see whether to take the attribute value or the text value of the node depends on whether the third parameter of the node clause has the @ sign.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.