SQL Server operations on XML Fields

T-SQL to operate XML data

I. Preface

SQL Server 2005 introduces a local data type called XML. You can create a table with one or more XML columns in addition to the relational columns. variables and parameters are also allowed. To better support XML model features (such as document order and recursive structure), XML values are stored as large binary objects (BLOB) in an internal format ).

You can use the XML string when saving an XML data to the database. SQL server automatically converts the string to the XML type and stores it in the database.

With SQL Server's support for XML fields, corresponding, T-SQL statements also provide a large number of XML operations to cooperate with the use of XML fields in SQL Server. This document describes how to use SQL statements to operate on XML.

Ii. Define XML Fields

During database design, we can easily define a field as the XML type in the Table Designer. Note that XML fields cannot be used as primary keys or index keys. Similarly, we can use SQL statements to create data tables that use XML fields. The following statement creates a table named "Docs, this strap has an integer primary key "Pk" and a non-typed XML column "xcol ":

Create Table Docs (Pk int primary key, xcol XML not null)

In addition to the table, the XML type can also appear in Stored Procedures, transactions, functions, and so on. Next we will complete the first step of XML operations. Use an SQL statement to define an XML-type data and assign values to it:

Declare @ xmldoc XML;

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

<Title> C program </title>

<Author> David </author>

<Price> 21 </price>

</Book>'

Iii. query operations

After defining an XML data type, we usually use query operations. The following describes how to use SQL statements to perform query operations.

In T-SQL, two functions are provided for querying XML data, namely query (XQuery) and value (XQuery, datatype) the data with tags is obtained, while the value (XQuery, datatype) is the TAG content. The following classes use these two functions for query.

1. Query (XQuery)

We need to obtain the title of the book and use query (XQuery) for query. The query statement is:

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

Running result

2. query using value (XQuery, datatype)

The title of the book is also obtained. To use the value function, you must specify two parameters, one being XQuery and the other being the type of the data to be obtained. See the following query statement:

Select @ xmldoc. Value ('(/book/Title) [1]', 'nvarchar (max )')

Running result

3. query attribute values

Whether using query or value, you can easily obtain a property value of a node. For example, we hope to obtain the ID of the book node. Here we use the Value Method for query, statement:

Select @ xmldoc. Value ('(/book/@ ID) [1]', 'nvarchar (max )')

Running result

4. query using xpath

XPath is a unified XML query statement supported by. net. You can use XPath to obtain the desired node without using the where statement. For example, we added another node to @ xmldoc and redefined it 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 preceding statement can be run independently, and the node with ID 0002 is obtained. The running result is as follows:

Iv. modification operations

SQL modification operations include update and delete. SQL provides the modify () method to modify XML. The parameter of the modify method is the XML modification language. The XML modification language is similar to SQL insert, delete, and update, but not the same.

1. Modify the node Value

We want to change the price of a book with the ID of 0001 to 100 so that 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. Running result

2. delete a node

Next, we will delete the node with ID 0002. The Code is as follows:

-- Delete the book node with the node ID 0002

Set @ xmldoc. Modify ('delete/root/book [@ ID = 0002] ')

Select @ xmldoc

Running result

3. Add nodes

In many cases, we also need to add nodes to the XML file. In this case, we also need to use the modify method. Next, we will add an ISBN node to the book node with ID 0001. The Code is as follows:

-- Add a node

Set @ xmldoc. Modify ('insert <ISBN> 78-596-134 </ISBN> before (/root/book [@ ID = 0001]/price) [1] ')

Select @ xmldoc. Query ('(/root/book [@ ID = "0001"]/ISBN )')

Running result

4. add and delete attributes

After you learn how to operate nodes, you will find that, in many cases, we need to operate nodes. At this time, we still use the modify method. For example, we add a date attribute to the book node with ID 0001 to store the publishing time. The Code is as follows:

-- Add attributes

Set @ xmldoc. Modify ('insert attribute Date {""} into (/root/book [@ ID = 0001]) [1] ')

Select @ xmldoc. Query ('(/root/book [@ ID = "0001"])')

Running result

If you want to add multiple attributes to a node at the same time, you can use a set of attributes. The set of attributes can be written as: (attribute Date "}, attribute year {"2008"}), you can add more. I will not give an example here.

5. Delete attributes

Delete an attribute. For example, you can use the following code to delete the ID attribute of the book node whose ID is 0001:

-- Delete attributes

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

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

Running result

6. modify attributes

Modifying the attribute value is also very common. For example, to change the ID attribute of the book node whose ID is 0001 to 0005, we can use the following code:

-- Modify attributes

Set @ xmldoc. Modify ('replace value of (root/book [@ ID = "0001"]/@ ID) [1] with "0005 "')

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

Running result

After the above learning, I believe you can use the XML type in SQL. below is what we did not mention. You can go to other places to check the exist () method, used to determine whether a specified node exists. The return value is true or false. The nodes () method, it is used to convert a group of nodes returned by a query into a group of record rows in a table similar to a result set.