Sp_xml_preparedocument of msdn

SQL Server 2005 books online
Sp_xml_preparedocument (Transact-SQL)	Send feedback
	See

Fold allExpand AllLanguage Filter: AllLanguage Filter: MultilingualLanguage Filter: Visual BasicLanguage Filter: C #Language Filter: C ++Language Filter: J #Language Filter: JScript

System Stored Procedure (Transact-SQL) > XML stored procedure ( SQL)>

Visual Basic (Declaration)

C #
C ++
J #
JScript

Read the Extensible Markup Language (XML) text provided as input, and then use the MSXML analyzer (msxmlsql. dll)
Analyze it and provide the analyzed documents for use. The analyzed document is a tree representation of nodes (elements, attributes, text, and comments) in the XML document.

Sp_xml_preparedocument returns a handle to access the newly created internal representation of the XML document. The handle is valid during the session duration, or
Sp_xml_removedocument makes it valid until the handle fails.

Note:
The analyzed documents are stored in the internal cache of SQL Server 2005. MSXML analyzer occupies SQL Server 1/8 of the total available memory. To avoid Memory insufficiency, run sp_xml_removedocument to release the memory.

 

Note:
To maintain backward compatibility, sp_xml_preparedocument will collapse CR (char (13) and LF (char (10) in the attribute )) Characters, even if these characters are materialized, they are collapsed.

 

Note:

You can use the XML analyzer called by sp_xml_preparedocument to analyze internal DTD and object declarations. Malicious DTD can be used And object declaration to execute DOS attacks. Therefore, we strongly recommend that you do not directly pass XML documents to untrusted sources Sp_xml_preparedocument. One way to reduce the possibility of such attacks is to check the XML Whether the object Declaration exists in the document, and then reject these documents before they are sent to the stored procedure.

 

Transact-SQL syntax conventions

Syntax

sp_xml_preparedocument hdoc OUTPUT [ , xmltext ] [ , xpath_namespaces ]

Parameters

Hdoc

The handle of the newly created document. Hdoc is an integer.

[Xmltext]

Is the original XML document. MSXML analyzer analyzes this XML document. Xmltext
Is a text parameter: Char, nchar, varchar, nvarchar, text, ntext, or XML. The default value is null. In this case, an empty XML file is created.
The internal representation of the document.

Note:
Sp_xml_preparedocument can only process text or untyped XML. If the input Instance value is already typed XML, first convert it to a new untyped XML instance, or convert it to a string, and then pass this value as the input. For more information, see .

 

[Xpath_namespaces]

Specifies the namespace Declaration used in the row and column XPath expressions of openxml. Xpath_namespaces
Is a text parameter: Char, nchar, varchar, nvarchar, text, ntext, or XML.

The default value is <Root
Xmlns: mp = "urn: schemas-microsoft-com: xml-metaprop">. Xpath_namespaces uses the correct format
The XML document provides the namespace URI for the prefix used in the XPath expression in OPENXML. The xpath_namespaces statement must use a prefix to reference a namespace.
Urn: schemas-microsoft-com: xml-metaprop; this will provide XML for the analysis
Element metadata. Although this technology can be used to redefine the namespace prefix for the meta-attribute namespace, The namespace will not be lost. Even if xpath_namespaces does not contain such declarations, the prefix
Mp is still valid for urn: schemas-microsoft-com: xml-metaprop.

Return code value

0 (successful) or> 0 (failed)

Permission

A public role is required.

Example
A. Prepare an internal representation for the correctly formatted XML document

The following example returns a handle to the new internal representation of the XML document provided as input:
sp_xml_preparedocumentWill use the default namespace prefix ing.

Copy code

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>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc -- Remove the internal representation. exec sp_xml_removedocument @hdoc

B. Prepare an internal representation for the XML document with the correct DTD format

The following example returns a handle to the new internal representation of the XML document provided as input: The Stored Procedure verifies the loaded document based on the DTD contained in the document. In
sp_xml_preparedocumentWill use the default namespace prefix ing.

	Copy code
DECLARE @hdoc int DECLARE @doc varchar(2000) SET @doc = ' <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE root [<!ELEMENT root (Customers)*> <!ELEMENT Customers EMPTY> <!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]> <root> <Customers CustomerID="ALFKI" ContactName="Maria Anders"/> </root>' EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

C. Specify the namespace URI

The following example returns a handle to the new internal representation of the XML document provided as input:
sp_xml_preparedocumentWillmpThe prefix is retained to the ing of the meta-attribute namespace, and
xyzAdd the ing prefix to the namespaceurn:MyNamespace.

Copy code

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>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'

See

XML Stored Procedure
SQL)
System stored procedures
(TRANSACT-SQL)
Sp_xml_removedocument
(TRANSACT-SQL)
Openxml
(TRANSACT-SQL)

Help and information