OpenXML
OpenXML provides a line set view through an XML document. Since OpenXML is a routine provider, OpenXML can be used in the Transact-SQL statement that appears in the rowset provider (such as a table, view, or OpenrowSet function).
grammar
OpenXML (idoc int [in], rowpattern nvarchar [in], [Flags Byte [in]]) [with (SchemadeClaration | Tablename)]
parameter
IDOC
It is the document handle of the internal expression method of the XML document. Create an internal representation of an XML document by calling sp_xml_preparedocument.
RowPattern
Is the XPath mode to identify nodes to be processed as rows (these nodes are passed by IDoc parameters).
Flags
Indicates that mapping should be used between XML data and relationship rows and how to fill overflow columns. Flag can be an optional input parameter, which can be one of the following values.
The byte value description 0 defaults to a property-centric mapping. 1 Use the property-centric mapping. In some cases, it can be used in combination with XML_ELEMENTS. The first map is first used in terms of use, and then uses the element-centric mapping for all columns still unprocessed. 2 Use an element-centric mapping. In some cases, it can be used in combination with XML_ATTRIBUTES. Apply a map-centric mapping first in terms of use, and then apply an element-centric mapping for all columns still unprocessed. 8 can be used in combination with XML_ATTRIBUTES or XML_ELEMENTS (logic or). In the context search, the flag indicates that the consumed data should not be copied to overflow attribute @mp: XMLText.
Schemadeclaration
Is the form of the form: colname colorn | metaproperty] [, colname collection [colpattern | metaproperty] ...]
ColName
Is the column name in the line.
COLTYPE
It is the SQL data type of the column column. If the column type is different from the basic XML data type of the characteristics, the type press is generated. If the type of column is
TimeStamp, the existing value in the XML document is ignored when selecting the OpenXML line, and the automatic filling value will be returned.
Colpattern
It is an optional general XPath mode that indicates how the XML node should be mapped to the column. If not specified
COLPATTERN, then a default mapping (by
Flags specified by the characteristics or centered on an elemental map).
The XPath mode specified as colpattern is used to specify a special mapping property (if the characteristic is centered on the center and the element-centric mapping), these special mapping properties can rewrite or enhance the default mapping specified by the flag.
The general XPath mode specified as colpattern also supports the metability.
Metaproperty
Is one of the meta properties provided by OpenXML. If the meta-inventory is specified, the column contains information provided by the meta-attribute. These meta properties enable you to extract information of the XML node (such as relative position, namespace information) to provide more detailed information than textual representation.
TableName
If the table having the desired architecture already exists and does not require column mode, it is a given table name (not a SchemADeclaration).
Provide a rowset format by using SchemADeclaration or specifying an existing TableName, the With clause provides a rowset format (other mapping information as needed). If you do not specify an optional With clause, then the result is returned in the Edge Table format. The edge table represents the fine structure of the XML document in a single table (eg, element / feature name, document hierarchy, namespace, PI, etc.). The following table describes the structure of the edge table.
Column Data Type Description IDBigint is the unique ID of the document node. The Id value of the root element is 0. Keep the negative ID value. The ParentidBigint identifies the parent node of the node. The parent node identified by this ID is not necessarily a parent element, but is NodeType depends on the NodeType of the child node identified by this ID. For example, if the node is a text node, its parent node may be a feature node. If the node is located on the top of the XML document, its ParentID is NULL. NodeTypeint identifies the node type. Is an integer corresponding to the XML DOM node type number (for node information, see DOM). The three node types are: 1 = element node 2 = Features Node 3 = Text Node LocalNamenVarchar gives a local name for element or characteristics. If the DOM object is not name, null is NULL. Prefixnvarchar is a namespace prefix for a node name. NamespaceurInvarchar is a node's namespace URI. If the value is NULL, the namespace does not exist. DataTypenvarchar is the actual data type of elements or characteristics, otherwise NULL. Introducing the data type from the embedded DTD or from the embedded architecture. Prevbigint is the XML ID of the former brothers element. If there is no brother element in front, null. TextnText contains the characteristic value or element content of the text format (if the edge table item does not require a value null).
Example
A. Use the SELECT statement with OpenXML.
The following example uses sp_XML_PREPAREDocument to create an internal representation of an XML image. Then execute the SELECT statement using the OpenXML rowset provider for the internal representation of the XML document.
The FLAG value is set to 1, indicating a mapping centering in characteristics. Therefore, the XML feature maps to the columns in the row. Specifies the
There is no specified optional colpattern (column mode) because the column name and XML feature name match.
The OpenXML Row Setup Provider creates a dual column set (CustomerID and ContactName), and the SELECT statement retrieves the necessary columns from the row (in this case all columns).
Declare @IDOC INT
Declare @doc varchar (1000)
Set @doc = '
'
--Create an internal representation of the xml document.
EXEC SP_XML_PREPAREDocument @idoc output, @doc
- Execute A Select Statement That Uses The OpenXML Rowset Provider.
SELECT *
From OpenXML (@IDOC, '/ root / Customer', 1)
With (Customerid Varchar (10),
ContactName VARCHAR (20))
The following is the result set:
Customerid ContactName
---------- --------------------
Vinet Paul Henriot
Lilas Carlos Gonzlez
If the Flags is set to 2 (indicated by an element-centric mapping) and perform the same SELECT statement, due to the
The following is the result set:
Customerid ContactName
---------- -----------
Null null
Null null
B. Specify colpattern for mapping between columns and XML features
The following query returns the characteristics of the customer ID, order date, product ID, and quantity from the XML document. RowPattern Identifies
Specify optional colpattern, indicating:
The ORDERID, CustomerId, and ORDERDATE columns in the row concentrate mapped to the characteristics of the parent node identified by RowPattern in the XML document. The PRODID column in the row is mapped to the ProductID feature, and the QTY column in the row set mapped to the Quantity feature of the node identified by RowPattern.
Although the mapping is specified by the Flag parameter, the mapping specified in colpattern is overwritten.
Declare @IDOC INT
Declare @doc varchar (1000)
Set @doc = '
ORDERDATE = "1996-07-04T00: 00: 00"> ORDERDATE = "1996-08-16t00: 00: 00"> ' --Create an internal representation of the xml document. EXEC SP_XML_PREPAREDocument @idoc output, @doc - Select Stmt Using OpenXML ROWSET Provider SELECT * From OpenXML (@IDOC, '/ root / customer / order / orderdetail ", 2) With (ORDERID INT '../@ORDERID', Customerid varchar (10) '../@customerid', ORDERDATE datetime '../@orderdate', ProductID ', Qty Int '@quantity') The results are as follows: OrderId Customerid OrderDate Product 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 C. Get the result of the edge table format In the following example, the WITH clause is not specified in the OpenXML statement. Therefore, the rows generated by OpenXML have edge table formats. SELECT statement returns all columns in the edge table. The example XML document in the following example consists of First call sp_xml_preparedocument to obtain a document handle. This document is passed to OpenXML. In the OpenXML statement RowPattern (/ root / customer) identifies the The final SELECT statement retrieves all columns in the edge table. Declare @IDOC INT Declare @doc varchar (1000) Set @doc = ' "1996-07-04T00: 00: 00"> "1996-08-16T00: 00: 00"> ' --Create an internal representation of the xml document. EXEC SP_XML_PREPAREDocument @idoc output, @doc - Select Statement Using OpenXML ROWSET Provider SELECT * From OpenXML (@IDOC, '/ root / customers') EXEC SP_XML_REMOVEDOCUMENT @IDOC The result is returned as an edge table.
