I. Introduction
started from SQL Server 2005, it increased the xml field type xml content that can be directly stored in the field, and the SQL Server treat it as as xml to treat, rather than as a varchar to be treated.
With SQL Server support for XML fields, corresponding, T-SQL statement also provides a number of functions for XML operations to match the XML fields in SQL Server be used. This article explains how to use SQL statements to operate on XML.
Second, the definition XML field
during database design, we can in the Table Designer, it is very convenient to define a field as XML type. Note that, XML field can not be used as a primary key or key. Similarly, we can also use SQL statements to create a data table using the XML field, the following statement creates a file called "docs" table, the table with integer primary key "pk" and untyped XML column "xCol" :
CREATE TABLE docs (pk INT PRIMARY KEY, xCol XML not null)
XML types in addition to use in the table, but also in stored procedures, services, functions, etc. appear. Let us complete the first step of our operation for XML, use the SQL statement defines an XML type data, and assign it a value:
set @ xmlDoc = '
select @ xmlDoc
three, XML field Precautions
- in SQL Server to Unicode (UTF-16) to store XML data.
- XML field can store up to 2G data.
- can insert strings like to write the contents of the XML field.
- xml data type instance when storing XML data, does not preserve the XML declaration (eg
- insert xml content of the order of attributes might change the order of the original xml instance.
- not preserve property values before and after the single and double quotes.
- not preserve namespace prefix.
- XML fields can be XML content indexing.
- XML fields can be created XML content constraints, such as age nodes must be greater than or equal 18.
- architecture by creating the XML typed, for example so that the following xml content
node must have nodes.
four, query operations
define an XML type of data, our most commonly used is the query operation, let's describe how to use SQL statements to query operation.
in the T-Sql, provides two types of data to XML query functions, namely query (xquery ) and value (xquery, dataType), wherein, query (xquery) is obtained with the tag data, and value (xquery, dataType) obtained is the label content. Next class we were to use these two functions to query.
1, using the query (xquery) query
we need to get the book's title (title), using the query (xquery) to query, the query is:
select@xmlDoc.query ('(books / book / title) [1]')
operating results as:
< / span>
2, using the value (xquery, dataType) query
also got the title of the book, using the value function, you need to specify two parameters, one for xquery, the other is to get the type of data. Look at the following query:
select@xmlDoc.value ('(books / book / title) [1]', 'nvarchar (max)')
operating results as:
3, the query attribute value
using either query or value, you can easily get the value of an attribute of a node, for example, we would like to get book node id, we are here Use value method to query statement is:
select@xmlDoc.value ('(books / book / @ id) [1]', 'nvarchar (max)')
operating results as:
< / span>
4, using xpath query
xpath 是. net platform support, unified Xml query. Using XPath can easily get the desired node, instead of using the where clause. For example,
- get the book node id 0002
select@xmlDoc.query ('(/ books / book [@ id = "0002"])')
above statement can be run independently, it gets the id to 0002 nodes. The results are
< / span>
five, modify operation
modification operations, including SQL update and delete. SQL provides the modify () method, to achieve the Xml modify operation. modify method parameters as XML to modify the language. Modify XML language similar to SQL Insert, Delete, UpDate, but not the same.
1, modify the node value
id 0001 we hope will be the price of the book (price) revised to 100, we can use the modify method. Code is as follows:
set@xmlDoc.modify ('replace value of (/ books / book [@ id = 0001] / price / text ()) [1] with " 100 "')
- get the book node id 0001
select@xmlDoc.query ('(/ books / book [@ id = "0001"])')
Note: modify method must appear in the set back. The result shown:
2, delete nodes
Next we remove the node id of 0002, as follows:
- delete the node id for the 0002 book node
set@xmlDoc.modify ('delete / books / book [@ id = 0002]')
select @ xmlDoc
operating results as:
< / span>
3, adding nodes
many cases, we also need to add nodes to the xml inside, this time we need to modify the same method. Here we have a book to the node id 0001 ISBN add a node, the code is as follows:
- Add Node
set@xmlDoc.modify ('insert
select@xmlDoc.query ('(/ books / book [@ id = "0001"] / isbn)')
operating results as:
< / span>
4, add and delete attributes
When you learn on the node after the operation, you will find that many times, we need to operate on a node. This time we are still using the modify method, for example, the node id for the 0001 book to add a date attribute is used to store time of publication. Code is as follows:
- add attributes
set@xmlDoc.modify ('insert attribute date {"2008-11-27"} into (/ books / book [@ id = 0001]) [ 1] ')
select@xmlDoc.query ('(/ books / book [@ id = "0001"])')
operating results as:
< / span>
Even if you want to add more properties to a node, you can use a set of attributes to achieve, a set of attributes can be written: (attribute date {" 2008-11-27 "}, attribute year {" 2008 "}), you can also add more. There is no longer an example of.
5, delete attribute
delete an attribute, such as deleting the node id for the 0001 book of the id attribute, we can use the following code:
- Delete Attribute
set@xmlDoc.modify ('delete books / book [@ id = "0001"] / @ id')
select@xmlDoc.query ('(/ books / book) [1]')
operating results as:
< / span>
6, modify the attribute
modify attribute values are also very common, for example, for the 0001 book node id id attribute changed to 0005, we can use the following code: < / p>
- modify attributes
set@xmlDoc.modify ('replace value of (books / book [@ id = "0001"] / @ id) [1] with " ; 0005 "')
select@xmlDoc.query ('(/ books / book) [1]')
operating results as:
< / span>
After the above study, I believe you can already well used in SQL Xml types, here we have not mentioned: exist () method, to determine whether the specified node exists, the return value is true or false; nodes () method, used to a set of nodes returned by an inquiry into a similar result set table in a set of rows. You can go to MSDN SEE http://msdn.microsoft. com/zh-cn/library/ms190798.aspx .
没有评论:
发表评论