SQL operations on XML fields

Original: SQL operations on XML fields

T-SQL manipulating XML Data

First, preface

SQL Server 2005 introduces a native data type called XML. A user can create a table that has one or more columns of XML type outside of the relational column, and also allows variables and parameters. To better support XML model features, such as document order and recursive structure, XML values are stored as large binary objects (BLOBs) in an internal format.

When a user stores an XML data into a database, the XML string can be used, and SQL Server automatically converts the string to an XML type and stores it in the database.

With SQL Server support for XML fields, the corresponding, T-SQL statements also provide a lot of functionality for XML operations to match the use of XML fields in SQL Server. This article mainly explains how to use SQL statements to manipulate XML.

Ii. Defining an XML field

In the design of the database, we can easily define a field as an XML type in the Table Designer. It is important to note that XML fields cannot be used as primary keys or as index keys. Similarly, we can use SQL statements to create a data table that uses an XML field, and the following statement creates a table named "Docs" with the integer primary key "PK" and the Untyped XML column "Xcol":

CREATE TABLE Docs (PK INT PRIMARY KEY, xcol XML not NULL)

XML types, in addition to being used in tables, can also appear in stored procedures, transactions, functions, and so on. Let's complete our first step to XML operations by defining an XML type of data using an SQL statement and assigning it a value:

declare @xmlDoc XML;

Set @xmlDoc = ' <book id= "0001" >

<title>c program</title>

<author>David</author>

<price>21</price>

</book> '

Third, the query operation

After defining an XML type of data, the most common is the query operation, below we describe how to use the SQL statement to do the query operation.

In T-SQL, there are two functions for querying XML type data, namely, query (XQuery) and value (XQuery, DataType), where query (XQuery) Gets the tagged data, and value (XQuery , DataType) is the content of the tag. Then we use these two functions to query each of the classes.

1 , querying with Query (XQuery)

We need to get the title of the book, using Query (XQuery) for querying, the query statement is:

Select @xmlDoc. Query ('/book/title ')

Run results

2 , using Value (XQuery, dataType) query

Also get the title of the book, using the value function, you need to specify two parameters, one for XQuery, and the other for the type of data to be obtained. Look at the following query statement:

Select @xmlDoc. Value (' (/book/title) [1] ', ' nvarchar (max) ')

Run results

3 , querying property values

Whether using query or value, it is easy to get a property value of a node, for example, we would like to get the ID of the book node, we use the value method to query, the statement is:

Select @xmlDoc. Value (' (/book/@id) [1] ', ' nvarchar (max) ')

Run results

4 , querying by using XPath

XPath is a unified XML query statement that is supported under the. NET Platform. Using XPath makes it easy to get the desired node without using the WHERE statement. For example, we have added another node in @xmldoc, redefined as follows:

Set @xmlDoc = ' <root>

<book id= "0001" >

<title>c# program</title>

<author>Jerry</author>

<price>50</price>

</book>

<book id= "0002" >

<title>java program</title>

<author>Tom</author>

<price>49</price>

</book>

</root> '

--Get the book node with ID 0002

Select @xmlDoc. Query (' (/root/book[@id = "0002"])

The above statement can run independently, and it gets the node with ID 0002. Running results such as:

Iv. Modification of operations

SQL modification operations include updates and deletions. SQL provides the modify () method for modifying the XML. The parameters of the Modify method are XML-modified languages. The XML modification language is similar to SQL's insert, Delete, UpDate, but not the same.

1 , modifying node values

We want to change the price of the book with ID 0001 to 100, so we can use the Modify method. The code is as follows:

Set @xmlDoc. Modify (' Replace value ' of (/root/book[@id =0001]/price/text ()) [1] with "100" ')

--Get the book node with ID 0001

Select @xmlDoc. Query (' (/root/book[@id = "0001"])

Note: The Modify method must appear after the set. Run results

2 , delete nodes

Next we delete the node with ID 0002, the code is as follows:

--Delete the book node with node ID 0002

Set @xmlDoc. Modify (' delete/root/book[@id = 0002] ')

Select @xmlDoc

Run results

3 , add Nodes

Many times, we also need to add nodes to the XML, this time we need to use the Modify method. Here we add an ISBN node to the book node with ID 0001, with the following code:

--Adding nodes

Set @xmlDoc. Modify (' Insert <isbn>78-596-134</isbn> before (/root/book[@id =0001]/price) [1] ')

Select @xmlDoc. Query (' (/root/book[@id = ' 0001 ']/isbn) ')

Run results

4 , adding, and deleting properties

When you learn to operate on a node, you will find that many times we need to operate on the node. At this point we still use the Modify method, for example, to add a date property to the book node with ID 0001, which is used to store the publication time. The code is as follows:

--Add attributes

Set @xmlDoc. Modify (' Insert attribute date{' 2008-11-27 "} into (/root/book[@id = 0001]) [1] ')

Select @xmlDoc. Query (' (/root/book[@id = "0001"])

Run results

If you want to add more than one property to a node at the same time, you can use a collection of properties that can be written as: (Attribute date{"2008-11-27"}, Attribute year{"2008"}), and you can add more. This is no longer an example.

5 , delete properties

To delete an attribute, such as the id attribute of the book node with ID 0001, we can use the following code:

--Delete attributes

Set @xmlDoc. Modify (' delete root/book[@id = "0001"]/@id ')

Select @xmlDoc. Query (' (/root/book) [1] ')

Run results

6 , modify Properties

Modifying property values is also very common, such as modifying the ID property of the book node with ID 0001 to 0005, which we can use as follows:

--Modify properties

Set @xmlDoc. Modify (' Replace value ' of (root/book[@id = "0001"]/@id) [1] with "0005" ')

Select @xmlDoc. Query (' (/root/book) [1] ')

Run results

OK, after the study above, I believe you can already use the XML type in SQL Well, here is not mentioned, you can go to other places to view: exist () method, to determine whether the specified node exists, the return value is true or false; nodes () method, Used to convert a set of nodes returned by a query into a set of record rows in a table similar to the result set.

Fei Qi
Date: November 27, 2008
Resources: SQL Server Books Online, MSDN, database and SQL Server 2005 Tutorials (Tsinghua University Press, Service的webgis)
The copyright belongs to the author, please specify the source of the reprint.

Updated on September 24, 2010 to modify the way content is displayed.

SQL operations on XML fields