How to interact with XML in SQL Server

How to interact with XML in SQL Server

XML can be said to be a flexible form of database performance. During database development
XML is required, especially for data exchange. In SQL Server
2005, XML has become a popular data type, XML has been made a new type of storage in the database. XML Schema-based support and server-based XML
The data validation function allows developers to easily modify stored XML documents remotely.

Some XML features are included in SQL Server 2000. The most common method is to use the for XML statement to return results in XML format. SQL
Server 2005 has different functions. In SQL Server
2005, XML is a real data type; this means that XML can be used as columns in tables and views, and XML can be used in T-SQL statements or as arguments to stored procedures. You can directly
Store, query, and manage XML files in the database.

In SQL Server, there are mainly the following ways to operate XML:

1) Use of for XML statements

For XML statements can be used in three modes: raw, auto, and explicit.

Let's take a look at these modes:

RAW mode:

In RAW mode, each row in the query result set is converted to an XML element with a common identifier or possibly providing an element name. By default, the row set is not null.
Each column value of is mapped to an attribute of the element. If you add the elements command to the for XML clause, each column value is mapped to the child element of the element.

First, let's look at the raw implementation method:

Create a table:

Create Table [DBO]. [tb_user] (
[ID] [int] identity (1, 1) not null,
[User_name] [varchar] (20) null,
[User_pwd] [varchar] (20) null,
Constraint [pk_user] primary key clustered
(
[ID] ASC
) With (pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key =
Off, allow_row_locks = on, allow_page_locks = on) on [primary]
) On [primary]

Statement 1:

Select ID, user_name, user_pwd from tb_user for XML raw

The output result is as follows:

<Row id = "1" user_name = "admin" user_pwd = "admin888"/>
<Row id = "2" user_name = "user" user_pwd = "user"/>

Statement 2:

Select ID, user_name, user_pwd from tb_user for XML raw, Elements

The output result is as follows:

<Row>
<ID> 1 </ID>
<User_name> admin </user_name>
<User_pwd> admin888 </user_pwd>
</Row>
<Row>
<ID> 2 </ID>
<User_name> User </user_name>
<User_pwd> User </user_pwd>
</Row>

Auto Mode:

The auto mode determines the shape of the XML returned by the query. When the nested element method is determined, the auto mode test method compares the column values in adjacent rows. In the from
Each table listed in at least one column in the select clause is expressed as an XML element. If the optional elements is specified in the for XML clause
Option. columns listed in the select clause are mapped to attributes or child elements.

Let's take a look at two implementation methods:

Statement 1:

Select ID, user_name, user_pwd from tb_user for XML auto

The output result is as follows:

<Tb_user id = "1" user_name = "admin" user_pwd = "admin888"/>
<Tb_user id = "2" user_name = "user" user_pwd = "user"/>

Statement 2:

Select ID, user_name, user_pwd from tb_user for XML auto, Elements

The output result is as follows:

<Tb_user>
<ID> 1 </ID>
<User_name> admin </user_name>
<User_pwd> admin888 </user_pwd>
</Tb_user>
<Tb_user>
<ID> 2 </ID>
<User_name> User </user_name>
<User_pwd> User </user_pwd>
</Tb_user>

Explicit mode:

In the explicit mode, the query writer controls the form of the XML document returned by the execution query. You must write a query in a specific way and explicitly specify additional information about the expected nesting as part of the query. When the explicit it mode is specified, the generated XML must comply with the syntax rules and be valid.

In the explicit mode, the row set generated by the query execution is converted to an XML document. To generate an XML document in the explicit mode, the row set must have a specific format. This requires the compilation of select queries to generate a row set (common table) with a specific format, so that the processing logic can then generate the required XML.

First, the query must generate the following two metadata columns:

The first column must provide the tag number of the current element (integer type), and the column name must be a tag. The query must provide a unique tag number for each element constructed from the row set.

The second column must provide the marker of the parent element, and the column name of this column must be parent. In this way, the tag and parent columns provide level information.

The first two columns are tag and parent, which are metadata columns. These values determine the hierarchy. The query must provide the column name in a specific way. The 0 or null in the parent column indicates that the corresponding element has no parent level.

When constructing XML, the processing logic selects a group of columns for each row and constructs an element.

Now let's take a look at the following two statements:

Statement 1:

Select 1 as tag,
Null as parent,
User_name as [tbuser! 1! Username],
User_pwd as [tbuser! 2! Userpwd]
From tb_user
For XML explicit

The output result is as follows:

<Tbuser username = "admin"/>
<Tbuser username = "user"/>

Statement 2:

Select 2 as tag,
Null as parent,
User_name as [tbuser! 1! Username],
User_pwd as [tbuser! 2! Userpwd]
From tb_user
For XML explicit

The output result is as follows:

<Tbuser userpwd = "admin888"/>
<Tbuser userpwd = "user"/>

In this example, columns tbuser! 1! Username And tbuser! 2! Userpwd forms a group, which is then used to construct elements. For the tag column value 1 In the first row and the row with the tag column value 2, the records of the corresponding table are generated according to the tag selection.

2) use openxml

Unlike the above, openxml mainly reads XML and can be converted to database data.

The openxml syntax is as follows:

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

IDOC: The document handle in the internal table form of the XML document.

Rowpattern: The XPath pattern used to identify the node to be processed as a row.

Flags: indicates the use of ing between XML data and relational row sets and how overflow columns should be filled.

0 is the "property-centric" ing by default.

1. Use the "property-centric" ing.

2. Use the "element-centric" ing.

8 can be used in combination with xml_attributes or xml_elements (logical or ).

With is followed by the corresponding field name and the corresponding attribute of the field.

Let's take a look at an example of Microsoft's call:

Declare @ dochandle int
Declare @ xmldocument nvarchar (1000)
Set @ xmldocument = n' <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 @ dochandle output, @ xmldocument
-- Execute a SELECT statement using openxml rowset provider.
Select *
From openxml (@ dochandle, '/root/customer', 1)
With (customerid varchar (10 ),
Contactname varchar (20 ))
Exec sp_xml_removedocument @ dochandle

Run the instance and the result is as follows:

Customerid contactname
------------------------------
Vinet Paul henriot
Lilas Carlos gonzlez

Now let's analyze the above example:

After defining the XML document of @ xmldocument, call the system program sp_xml_preparedocument, which indicates that when the XML document has memory
And allow the XML document as the call parameter. Then you can call the openxml function. The
Read the values of customerid and contactname, and then call the system program sp_xml_removedocument to delete the SQL Server
XML data in the server memory.

But pay attention to the memory usage when calling the openxml function. The returned value of the system program sp_xml_preparedocument is the handle to the XML document in the memory. Therefore, do not use openxml to load large XML documents. Otherwise, memory overflow may occur on the server.

3) XQuery for XML Data Types

XQuery is a language that can query structured or semi-structured XML data. Because the SQL Server 2005 database engine supports XML data types, you can store documents in the database and use XQuery for query.

Here we only give a simple example:

Declare @ x XML
Set @ x = '<myroot> <element1> first </element1> <element2> second </element2>
</Myroot>'
Select @ X. Query ('/myroot ')

The output result is as follows:

<Myroot>
<Element1> first </element1>
<Element2> second </element2>
</Myroot>

Declare @ x XML
Set @ x = '<myroot> <element1> first </element1> <element2> second </element2>
</Myroot>'
Select @ X. Query ('/myroot/element1 ')

The output result is as follows:

<Element1> first </element1>

It can be seen that it outputs the corresponding value by reading the node of the XML document.

The XQuery function is very powerful and involves a wide range of content. If you are interested, you can view the detailed introduction of XQuery in SQL Server 2005 books online.

Although the XML data type in SQL Server 2005 is treated like many other data types, it still has the following restrictions:

· The XML type cannot be converted to the text or ntext data type.

· No other data types except the string type can be converted to XML.

· XML Columns cannot be used in group by statements.

· The distributed local view cannot contain XML data types.

· SQL _variant instances cannot use XML as a seed type.

· The XML Column cannot be a primary key or a part of a foreign key.

· The XML Column cannot be unique.

· The collate clause cannot be used in XML columns.

· XML Columns cannot be added to rules.

· The only built-in scalar functions that can be applied to XML columns are isnull and coalesce. No other built-in scalar functions support the XML type.

· A table can contain up to 32 XML columns.

· A table with XML Columns cannot have a primary key with more than 15 columns.

· Tables with XML Columns cannot have a timestamp data type as part of their primary keys.

· The XML stored in the database only supports 128 levels.