(download) Operations for SQL Server XML fields

Translated from: http://blog.csdn.net/hliq5399/article/details/8315373

Today the XML fields for SQL Server are used, first the XML fields in this project are stored in the ntext field, so the first operation ntext is converted to XML

The following excerpt from

Http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/09/18/sql2005-nvarchar-to-xml.aspx

XMLString is a ntext linked fields, which is stored inside the content from the *.xml to read the content, try to convert the Xmlstringcol into XML will be wrong:

SELECT TOP 1 CONVERT (XML, xmlstring) from myTable

MSG 9402, Level A, State 1, line 1
XML Parsing:line 1, character, unable to switch the encoding

After some tests, I always figured out where the problem was. From *.xml, the first column is the XML declaration <?xml version= "1.0" encoding= "UTF-8", and remember? SQL has always been used to UCS-2 storage, which forms a contradiction: clearly encoding is UCS-2, but the XML content but also declared themselves to be UTF-8, led to conversion failure.

The solution I came up with was to get rid of encoding and let SQL do it by itself, but since the linked fields bit was ntext, it had to be turned into nvarchar (MAX) to switch to the prince with the replace cat, and the instruction was changed to:

SELECT TOP 1 convert (XML, REPLACE (CONVERT (NVARCHAR (MAX), xmlstring), ' encoding= ' UTF-8 "', ') ' from myTable

The above explanation is good, not much nonsense, the following is the operation of the XML field

Http://www.cnblogs.com/youring2/archive/2008/11/27/1342288.html

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. Query using value (XQuery, DataType)

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. Using XPath for querying

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. Modify the node value

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. Deleting 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. Adding 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 Removing attributes

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 Attributes

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. Modifying 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.

(download) Operations for SQL Server XML fields