SQL support for XML

SQL provides powerful support for XML

There are two main points:

1. Convert the select result to XML

(1) For XML raw this will return each row of data in the result set as a single data element. The element name is row, and the data items in each row are used as the row attributes.
(2) For XML auto will mark each data element with the table name or data source name.
(3) For XML explicit can define the hierarchy of returned data so that each segment belongs to a specific level and then perform formatted queries.
(4) change the display tag to Chinese
(5) multiple layers of the same table
(6) multiple layers of different tables
(7) null values for processing data and dates

(8.) In addition, Fox XML can also be used as a value assignment statement.
============================
The data in the person table of the database is
Personname personage
Lisi 30
Zhangsan 30

(1.) ---------- [raw] ---------
Select [personname], [personage]
From [testdb]. [DBO]. [person]
For XML raw

Result:
<Row personname = "Lisi" Personage = "30"/>
<Row personname = "zhangsan" Personage = "30"/>

(2.) ---------- [auto] --------
Select [personname], [personage]
From [testdb]. [DBO]. [person]
For XML auto

Result:
<Testdb. DBO. Person personname = "Lisi" Personage = "30"/>
<Testdb. DBO. Person personname = "zhangsan" Personage = "30"/>

(3.) ----------- [explicit] --------
Select 1 as tag, null as parent
, Rtrim (personname) as [personbasic! 1! Personname]
, Rtrim (personage) as [personbasic! 1! Personage! XML]
From [testdb]. [DBO]. [person]
For XML explicit

Result:

<Personbasic personname = "Lisi">
<Personage> 30 </personage>
</Personbasic>
<Personbasic personname = "zhangsan">
<Personage> 30 </personage>
</Personbasic>

 

The path can control layers more flexibly.

(4.) ----- change the display tag to Chinese -----
Select
1 As tag,
Null as parent,
Personname as [personnel! 1! Name! XML],
Personage as [personnel! 1! Age! XML]
From person
For XML explicit

Result:

<Personnel>
<Name> Lisi </Name>
<Age> 30 </age>
</Personnel>
<Personnel>
<Name> zhangsan </Name>
<Age> 30 </age>
</Personnel>

(5.) ----- multiple layers in the same table ----
Select
1 As tag,
Null as parent,
Rtrim (A. personname) as [personnel! 1! Name],
Null as [personnel information! 2! Age! XML]
From person

Union all

Select
2 As tag,
1 As parent,
Rtrim (A. personname ),
B. personage
From person B, person
Where a. personname = B. personname

Order by [personnel! 1! Name], tag

For XML explicit

Result:

<Personnel name = "Lisi">
<Personnel information>
<Age> 30 </age>
</Personnel Information>
</Personnel>
<Personnel name = "zhangsan">
<Personnel information>
<Age> 30 </age>
</Personnel Information>
</Personnel>

(6.) -------- multi-layer table -------
Select
1 As tag,
Null as parent,
Rtrim (A. personname) as [personnel! 1! Name],
Null as [personnel information! 2! Age! XML],
Null as [personnel information! 2! Occupation! XML]
From person

Union all

Select
2 As tag,
1 As parent,
Rtrim (A. personname ),
B. personage,
Rtrim (B. personjob)
From personinfo B, person
Where B. personname = A. personname
Order by [personnel! 1! Name], tag
For XML explicit

Result:

<Personnel name = "Lisi">
<Personnel information>
<Age> 30 </age>
<Occupation> teacher </occupation>
</Personnel Information>
</Personnel>
<Personnel name = "zhangsan">
<Personnel information>
<Age> 30 </age>
<Occupation> worker </occupation>
</Personnel Information>
</Personnel>

(7.) ------- null values for processing data and dates -------
The data in the person table of the database is
Personname personage personbirth (can be empty)
Lisi 30 1987-06-06
Zhangsan 30

When the field in the data table is null
The generated XML document does not contain this node.
To solve this problem
When necessary, you can set the number and date type
Convert to string type
In this way, you can receive empty strings.
(But I don't know how it works)

Select
1 As tag,
Null as parent,
Rtrim (personname) as [personnel! 1! Name! XML],
Rtrim (isnull (convert (char, personage), '') as [personnel! 1! Age! XML],
Rtrim (isnull (convert (char (10), personbirth, 120), '') as [personnel! 1! Date of birth! XML]
From person
For XML explicit

Result:

<Personnel>
<Name> Lisi </Name>
<Age> 30 </age>
<Date of Birth> </Date of Birth>
</Personnel>
<Personnel>
<Name> zhangsan </Name>
<Age> </age>
<Date of Birth> </Date of Birth>
</Personnel>

(8.) In addition, Fox XML can also be used as a value assignment statement.

Declare @ x XML
Set @ x = (select *
From sales. Customer
For XML auto, type)
Select @ x

 

2. Search openxml from XML

 

Openxml provides the row set view through the XML document. Because openxml is a row set provider, you can use openxml in a Transact-SQL statement that displays a row set provider (such as a table, view, or OpenRowSet function.

Syntax

Openxml (IDOC int [in], rowpattern nvarchar [in], [flags byte [in])
[With (schemadeclaration | tablename)]

Parameters

IDOC

Is the document handle of the XML document's internal table method. Call sp_xml_preparedocument to create the internal table method of the XML document.

Rowpattern

It is an XPATH pattern used to identify the nodes to be processed as rows (in XML documents, the handles of these nodes are passed by the IDOC parameter ).

Flags

Ing between XML data and relational row sets and how overflow columns should be filled. Flag is an optional parameter, which can be one of the following values.

Byte value

Description

0
The default value is feature-centric ing.

1
Use feature-centric ing.
In some cases, it can be used in combination with xml_elements. In use, the feature-centric ing is applied first, and then the element-centric ing is applied to all columns that are not yet processed.

2
Use element-centric ing.
In some cases, it can be combined with xml_attributes. Apply feature-centric ing before applying element-centric ing to all columns that are not yet processed.

8
It can be used in combination with xml_attributes or xml_elements (logical or ).
In the context of the search, this flag indicates that the consumed data should not be copied to the overflow attribute @ MP: xmltext.

 

 

 

Schemadeclaration

Is the schema definition of the form:
Colname coltype [colpattern | metaproperty] [, colname coltype [colpattern | metaproperty]...]

 

Colname

Is the name of the column in the row set.

Coltype

Is the SQL data type of columns in a row set. If the column type is different from the basic XML data type of the feature, type suppression occurs. If the column type is timestamp, When you select from the openxml row set, the existing values in the XML document are ignored and the auto-filled values are returned.

Colpattern

It is an optional generic XPath mode that describes how to map XML nodes to columns. If colpattern is not specified, a default ing occurs (feature-centric or element-centric ing specified by flags ).

The XPath mode specified as colpattern is used to specify special ing properties (if feature-centric and element-centric ing occurs ), these special ing properties can override or enhance the default ing specified by the flag.

The generic XPath mode specified as colpattern also supports metadata.

 

Metaproperty

Is one of the Meta Attributes provided by openxml. If the metadata attribute is specified, the column contains the information provided by the metadata attribute. These meta attributes allow you to extract information about XML nodes (such as relative positions and namespace information) to provide more detailed information than regionalization.

Tablename

If a table with the expected schema already exists and the column mode is not required, the given table name (instead of schemadeclaration) is used ).

By using schemadeclaration or specifying an existing tablename, The with clause provides a row set format (Other ing information is also provided as needed ). If no optional with clause is specified, results are returned in edge table format. Edge tables represent the fine-grained structure of XML documents (such as element/feature names, document hierarchies, namespaces, and PI) in a single table ).

The following table describes the structure of the edge table.

Column name
Data Type
Description

ID
Bigint
Is the unique ID of the document node.

The ID of the root element is 0. Retain the negative id value.
 

Parentid
Bigint
Identifies the parent node of a node. The parent node identified by this ID is not necessarily a parent element, but is determined by the nodetype of the child node of the node identified by this ID. For example, if a node is a text node, its parent node may be a feature node.

If the node is located at the top layer of the XML document, its parentid is null.
 

Nodetype
Int
Identifies the node type. Is an integer corresponding to the xml dom node type number (for node information, see DOM ).

Three node types are:

1 = Element Node
2 = feature node
3 = text node
 

Localname
Nvarchar
Specifies the local name of an element or feature. If the DOM object does not have a name, it is null.

Prefix
Nvarchar
Is the namespace prefix of the node name.

Namespaceuri
Nvarchar
Is the node namespace URI. If the value is null, The namespace does not exist.

Datatype
Nvarchar
Is the actual data type of the element or feature line. Otherwise, it is null. Deduce the data type from the embedded DTD or from the embedded architecture.

Prev
Bigint
Is the xml id of the previous sibling element. If no sibling element exists, it is null.

Text
Ntext
Contains feature values or element content in text format (null if the edge table item does not need a value ).

 

 

 

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 the feature-centric ing. Therefore, the XML feature maps to columns in the row set. The rowpattern specified as/root/customers identifies the <customers> node to be processed.

No optional colpattern is specified because the column name matches the XML feature name.

The openxml row set provider creates a customerid and contactname row set. The SELECT statement retrieves necessary columns from the row set (retrieve all columns in this example ).

Declare @ IDOC 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 @ 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 you set flags to 2 (indicating element-centric ing) and execute the same SELECT statement, because the <customers> element does not have any child element, the customerid and contactname values of two customers in the XML document are returned as null.

The following is the result set:

Customerid contactname
---------------------
Null null
Null null

B. Specify colpattern FOR THE ing between columns and XML features

The following query returns the customer ID, order date, product ID, and quantity from the XML document. Rowpattern <orderdetail> element. Productid and quantity are the features of <orderdetails> elements. Customerid and orderdate are the features of the parent element (<orders>.

Specify the optional colpattern, indicating:

 

The attributes of the orderid, mermerid, and orderdate columns in the row set are mapped to the parent node of the node identified by rowpattern in the XML document.

 

The prodid column in the row set maps to the productid feature, while the qty column in the row set maps to the quantity feature of the node identified by rowpattern.

Although the element-centric ing is specified by the flag parameter, the ing specified in colpattern overwrites the flaing.

Declare @ IDOC 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 = "11" quantity = "12"/>
<Orderdetail productid = "42" quantity = "10"/>
</Order>
</Customer>
<Customer customerid = "Lilas" contactname = "Carlos gonzlez">
<Order orderid = "10283" customerid = "Lilas" employeeid = "3"
Orderdate = "1996-08-16t00: 00: 00">
<Orderdetail productid = "72" quantity = "3"/>
</Order>
</Customer>
</Root>'
-- 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 ',
Prodid int '@ productid ',
Qty int '@ quantity ')

The result is as follows:

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

C. obtain the results of the edge table.

In the following example, The with clause is not specified in the openxml statement. Therefore, the row set generated by openxml has the edge table format. The SELECT statement returns all columns in the edge table.

The example XML document in the following example is composed of <customer>, <order>, and <order_0020_details> elements.

Call sp_xml_preparedocument to obtain the document handle. The document handle is passed to openxml.

In openxml statements

 

Rowpattern (/root/customer) identifies the <customer> node to be processed.

 

The with clause is not provided. Therefore, openxml returns the row set as an edge table.

Finally, the SELECT statement Retrieves all columns in the edge table.

Declare @ IDOC int
Declare @ Doc varchar (1000)
Set @ Doc ='
<Root>
<Customers customerid = "Vinet" contactname = "Paul henriot">
<Orders customerid = "Vinet" employeeid = "5" orderdate =
"1996-07-04t00: 00: 00">
<Order_x0020_details orderid = "10248" productid = "11" quantity = "12"/>
<Order_x0020_details orderid = "10248" productid = "42" quantity = "10"/>
</Orders>
</Customers>
<Customers customerid = "Lilas" contactname = "Carlos gonzlez">
<Orders customerid = "Lilas" employeeid = "3" orderdate =
"1996-08-16t00: 00: 00">
<Order_x0020_details orderid = "10283" productid = "72" quantity = "3"/>
</Orders>
</Customers>
</Root>'
-- 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/customer ')
Exec sp_xml_removedocument @ IDOC
The result is returned as an edge table.