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