SQL Server uses T-SQL to manipulate XML data

First, the preface

SQL Server 2005 introduces a native data type called XML. Users can create tables that have one or more XML-type columns in addition to the relational column, and also allow 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 in a database, the XML string can be used, and SQL Server automatically converts the string into an XML type and is stored in the database.

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

Ii. Defining XML fields

In the design of the database, we can easily define a field as an XML type in the Table Designer. It should be noted 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, and the following statement creates a table named "Docs" with an integer primary key "PK" and an untyped XML column "Xcol":

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

In addition to being used in tables, XML types can also occur in stored procedures, transactions, functions, and so on. Let's do the first step in our XML operation by using the SQL statement to define an XML type of data and assign it a value:

declare @xmlDoc XML;  
      
Set @xmlDoc = ' <bookid= ' 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, we will explain how to use SQL statements to query operations.

In T-SQL, there are two functions that query XML type data, query (XQuery) and value (Xquery,datatype), where query (XQuery) gets tagged data, and

Value (Xquery,datatype) is the content of the label. Then we use these two functions separately to query.

1. Query using query (XQuery)

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

declare @xmlDoc XML;  
      
                                   Set @xmlDoc = ' <root> <bookid= ' 0001 ' >   
      
                                   <title>C#Program</title> <author>Jerry</author> <price>50</price> </book&  
      
                            Gt  
      
                                   <bookid= "0002" > <title>JavaProgram</title> <author>Tom</author> <price>49&lt ;/price> </book> </root> ' Select @xmlDoc. Query ('/root/book/title ') Select @xmlDoc. Query (' (//title) [2] ') Select @xmlDoc. Query (' root/book[  
     1]/title) Select @xmlDoc. Query (' (/root/book/title) [1] ') 
Select @xmlDoc. Query ('/root/book[position () <2]/title ') declare @id varchar (max) = ' 0001 ' Select @xmlDoc. Query ('/root/book[@id =sql:variable ("@id")]/title ')