SQL Server uses XML verbose tutorial

The core of SQL Server support for XML is the format of XML data, which can store XML data in objects in the database, such as variables, columns, and parameters. When you configure one of these objects with an XML data type, you specify the type name as you specify a type in SQL Server.

The data type of XML ensures that your XML data is well built and is compliant with ISO standards. Before defining an XML data type, we first need to know several of its limitations, as follows:

An XML column for an instance cannot contain more than 2GB of data.
An XML column cannot be an index.
An XML object cannot use a clause in GROUP by.
The data type of XML does not support comparisons and sorting.

define an XML variable

DECLARE @ClientList XML
SET @ClientList =
' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?>
<!--A List of current clients-->
<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People> '
SELECT @ClientList
Go

This example uses the declare declaration to define a variable named @clientlist, and when I declare a variable, only the name of the data type containing the XML is required after the variable name.

I set the value of the variable and then use Select to retrieve the value. As we think, it returns the XML document. As follows:

<!--A List of current clients-->
<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>


Next we'll look at how to define a column of XML

In the following example, I will create a table of store customers that stores the ID and customer information for each store.

Use ADVENTUREWORKS2008R2
Go
IF object_id (' dbo. Storeclients ') is not NULL
DROP TABLE dbo. Storeclients
Go
CREATE TABLE dbo. Storeclients
(
StoreID INT IDENTITY PRIMARY KEY,
Clientinfo XML not NULL
)
Go

Next, insert the data into this table, including the XML documents and fragments. I'm going to declare an XML variable and then use that variable to insert the document into the table's data row.

DECLARE @ClientList XML
SET @ClientList =
' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?>
<!--A List of current clients-->
<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People> '
INSERT into dbo. Storeclients (Clientinfo)
VALUES (@ClientList)
Go

Although the variable inserts the entire XML document in, it is inserted into the table column as a single value.

As described above, both creation and insertion are straightforward, and then we'll look at how to create an XML parameter

define an XML parameter

For example, I define @storeclients as an input parameter and configure it as the type of XML

Use ADVENTUREWORKS2008R2
Go
IF object_id (' dbo. Addclientinfo ', ' P ') is not NULL
DROP PROCEDURE dbo. Addclientinfo
Go
CREATE PROCEDURE dbo. Addclientinfo
@StoreClients XML
As
INSERT into dbo. Storeclients (Clientinfo)
VALUES (@StoreClients)
Go

Then we'll look at how XML can be used as a parameter in a stored procedure:

DECLARE @ClientList XML
SET @ClientList =
' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?>
<!--A List of current clients-->
<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People> '
EXEC dbo. Addclientinfo @ClientList

The process is also straightforward, first assigning the XML data to the variable, and then executing the variable as a parameter to the SP, which is the query you will find that the data is already in the table.

Now we're going to take a look at the methods supported by XML types: query (), value ().

Before that, we need to know an expression that is XQuery, a powerful scripting language used to get XML data. SQL Server supports a subset of this language, so we can use expressions in this language to retrieve and modify XML data.
Attention:

Because XQuery is a very complex language, we just cover a subset of his components, and if you want to further understand how it applies, check out MSDN XQuery Language Reference.

So let's take a look at how query () and value two methods use XML data in the first instance. It should be noted that my next test environment is SQLServer2008 R2. The instance contains the CLIENTDB database, the clientinfocollection XML data, and the Clientinfo table.

Use master;
Go

IF db_id (' Clientdb ') is not NULL
DROP DATABASE Clientdb;
Go

CREATE DATABASE Clientdb;
Go

Use Clientdb;
Go

IF object_id (' clientinfocollection ') is not NULL
DROP XML SCHEMA COLLECTION clientinfocollection;
Go

CREATE XML SCHEMA COLLECTION clientinfocollection as
' <xsd:schema xmlns:xsd= ' Http://www.w3.org/2001/XMLSchema '
Xmlns= "Urn:clientinfonamespace"
Targetnamespace= "Urn:clientinfonamespace"
elementformdefault= "qualified" >
<xsd:element name= "People" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name= "Person" minoccurs= "1" maxoccurs= "unbounded" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name= "FirstName" type= "xsd:string" minoccurs= "1" maxoccurs= "1"/>
<xsd:element name= "LastName" type= "xsd:string" minoccurs= "1" maxoccurs= "1"/>
<xsd:element name= "Favoritebook" type= "xsd:string" minoccurs= "0" maxoccurs= "5"/>
</xsd:sequence>
<xsd:attribute name= "id" type= "xsd:integer" use= "required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema> ';
Go

IF object_id (' Clientinfo ') is not NULL
DROP TABLE Clientinfo;
Go

CREATE TABLE Clientinfo
(
ClientID INT PRIMARY KEY IDENTITY,
Info_untyped XML,
info_typed XML (clientinfocollection)
);

INSERT into Clientinfo (info_untyped, info_typed)
VALUES
(
' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?>
<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People> ',
' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?>
<people xmlns= "Urn:clientinfonamespace" >
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People> '
);

Listing 1: Creating Test environments and data

The XML query () method

The query method, typically used to return an untyped XML instance that specifies a subset of XML, such as the following, to implement an expression with parentheses and single quotes, syntax:

Db_object.query (' Xquery_exp ')

When we call this method, replace the expression within the quotation mark with the real database object. Compare the results with examples to see what is different.

SELECT info_untyped.query ('/people ')
As people_untyped
From Clientinfo;

Listing 2: Use Query () to get the value of the <People> element

In this case, all elements under the label, including child element attributes and their values, are returned.

<People>
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>

Listing 3: The result set returns the contents of/people

If you intend to retrieve the contents of the <People> elements in a typed column, I need to modify the XQuery expression. such as Listing 4

SELECT Info_typed.query (
' Declare namespace ns= ' Urn:clientinfonamespace ';
/ns:people ') as people_typed
From Clientinfo;

Listing 4: Use Query () to retrieve typed XML columns, and then you run the statement and you get results like Listing5

<people xmlns= "Urn:clientinfonamespace" >
<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>

Listing 5: Show Results

As above, we find that the two results are very close, the only difference being that the typed columns contain the namespaces involved.

If we're going to get the sub level, the child element content, we need to modify the expression by adding/person to the path name, as follows:

SELECT
Info_untyped.query (
'/people/person ') as people_untyped,
Info_typed.query (
' Declare namespace ns= ' Urn:clientinfonamespace ';
/ns:people/ns:person ') as people_typed
From Clientinfo;

Listing 6: Retrieving <Person> elements

<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<person id= "5678" >
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>

Listing 7: Results of untyped data for this result set

<ns:person xmlns:ns= "Urn:clientinfonamespace" id= "1234" >
<ns:FirstName>John</ns:FirstName>
<ns:LastName>Doe</ns:LastName>
</ns:Person>
<ns:person xmlns:ns= "Urn:clientinfonamespace" id= "5678" >
<ns:FirstName>Jane</ns:FirstName>
<ns:LastName>Doe</ns:LastName>
</ns:Person>

Listing 8: This result set is the result of typed data

If we are going to get an element of the specified <Person> below, we need to add the id attribute involved. The following compares types and non-type situations to specify how the element attributes are fetched.

SELECT
Info_untyped.query (
'/people/person[@id =1234] ' as people_untyped,
Info_typed.query (
' Declare namespace ns= ' Urn:clientinfonamespace ';
/ns:people/ns:person[@id =5678] as people_typed
From Clientinfo;

Listing 9: Retrieving data, specifying elements

There is no change in the preceding, add an expression to the element, and then use the brackets to add the value of the @id in the brackets, and the results are as follows

<person id= "1234" >
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>

The Listing 10:id returns a value for 1234 untyped data results.

For typed columns, I use an ID of 5678. Note that this time it is no longer necessary to prefix the namespace with the attribute name, just refer to the element name before it is sufficient.

<ns:person xmlns:ns= "Urn:clientinfonamespace" id= "5678" >
<ns:FirstName>Jane</ns:FirstName>
<ns:LastName>Doe</ns:LastName>
</ns:Person>

Data results for Listing 11:id 5678

Further presentation results, down-level

SELECT
Info_untyped.query (
'/people/person[@id =1234]/firstname ') as people_untyped,
Info_typed.query (
' Declare namespace ns= ' Urn:clientinfonamespace ';
/ns:people/ns:person[@id =5678]/ns:firstname ') as people_typed
From Clientinfo;

Results

<FirstName>John</FirstName>

<ns:firstname xmlns:ns= "Urn:clientinfonamespace" >Jane</ns:FirstName>

Listing 14: Show of the results of the name

Of course, it can also be displayed by means of a digital index:

SELECT
Info_untyped.query (
'/people/person[1]/firstname ') as people_untyped,
Info_typed.query (
' Declare namespace ns= ' Urn:clientinfonamespace ';
/ns:people/ns:person[2]/ns:firstname ') as people_typed
From Clientinfo;

Listing 15: Using a numeric index to refer to the results under an element

the value () method of XML

As simple as the query () method, you can use value () many times when you want to retrieve a particular element or attribute instead of getting the XML element. This method only returns a specific value, not as a data type. So be sure to pass two parameter XQuery expressions and T-SQL data types. Here's a look at the syntax:

Db_object.value (' Xquery_exp ', ' Sql_type ')

SELECT
Info_untyped.value (
' (/people/person[1]/firstname) [1] ',
' varchar ') as name_untyped,
Info_typed.value (
' Declare namespace ns= ' Urn:clientinfonamespace ';
(/ns:people/ns:person[2]/ns:firstname) [1] ',
' varchar ') as name_typed
From Clientinfo;

Listing 16: Retrieving the value of <FirstName>

In Listing16, I specified [1] after the XQuery expression, so the result set returns only the first person's name.

name_untyped name_typed
-------------------- --------------------
John Jane

Listing: <FirstName> Two results

Of course, we can also retrieve the attribute value of the ID of each instance and specify that the int type returns.

SELECT
Info_untyped.value (
' (/people/person/@id) [1] ',
' int ') as name_untyped,
Info_typed.value (
' Declare namespace ns= ' Urn:clientinfonamespace ';
(/ns:people/ns:person/@id) [2] ',
' int ') as name_typed
From Clientinfo;

Listing 19: Retrieves the id attribute value of two instances

name_untyped name_typed
-------------------- --------------------
1234 5678

Listing 20: Returns properties of two IDs

In addition to defining your XQuery expression in an expression, you can aggregate functionality to further define your query and operational data. For example, the Count () feature, we'll get the number of <Person> elements in each column.

SELECT
Info_untyped.value (
' Count (/people/person) ',
' int ') as number_untyped,
Info_typed.value (
' Declare namespace ns= ' Urn:clientinfonamespace ';
Count (/ns:people/ns:person) ',
' int ') as number_typed
From Clientinfo;

Listing 21: Use the Count feature to retrieve the number of elements

The results are as follows:

number_untyped number_typed
-------------- ------------
2 2

Listing 22: Number of <Person> elements per column of data

Another common feature is concat (), which can connect data under two or more XML elements. You can specify each part that you want to connect to. Example:

SELECT
Info_untyped.value (
' Concat (/people/person/firstname) [2], "",
(/people/person/lastname) [2]) ',
' varchar ') as FullName
From Clientinfo;

Listing 23: Use Concat () to connect values

FullName
-------------------------
Jane Doe

Listing 24: The return value after the connection

The first and last names are concatenated together to form a single value. comes from the same <Person>, and of course it can come from different.

Summary

We have a basic understanding of the simple application of XML in SQL Server, from definitions to usage methods. You also see a subset of query () retrieval, and you can use value () to retrieve values for independent element properties. Of course, in addition to the exist () Andnodes () Such a method, with the syntax of the application, this part is no longer to start speaking, the same. If you don't understand, you can talk privately. For more use, also visit MSDN to get (Search XQuery Language Reference).

SQL Server operations on XML fields

T-SQL Operation 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, xcol XML 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 = '

David

21st

'

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, namely 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:

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

Run the results as shown in figure:

2. Use value (XQuery, DataType) query

The same is 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. Look at the following query statement:

Select @xmlDoc. Value (' (/book/title) [1] ', ' nvarchar (max) ')

Run the results as shown in figure:

3. Query attribute value

Whether you're using query or value, it's easy to get a property value for a node, for example, we'd like to get the ID of the book node, where we use the value method to query:

Select @xmlDoc. Value (' (/book/@id) [1] ', ' nvarchar (max) ')

Run the results as shown in figure:

4. Using XPath for query

XPath is a unified XML query statement supported under the. NET Platform. Using XPath makes it easy to get the desired node without using the WHERE statement. For example, we added another node to the @xmldoc and redefined the following:

Set @xmlDoc = '

Jerry

50

Tom

49

'

--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. The results of the operation are shown below:

Iv. Modifying operations

SQL modification operations include updates and deletes. SQL provides a modify () method to implement modifications to the XML. The parameters of the Modify method are XML-modified languages. The XML modification language is similar to SQL INSERT, Delete, UpDate, but not the same.

1, modify the value of the node

We want to change the price of the book with ID 0001 to 100, and 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 the results as shown in figure:

2, delete the node

Next, we'll remove the node with ID 0002, and the code is as follows:

--Delete the book node with node ID 0002

Set @xmlDoc. Modify (' delete/root/book[@id = 0002] ')

Select @xmlDoc

Run the results as shown in figure:

3. Add nodes

Most of the time, we also need to add nodes to the XML, and this time we need to use the Modify method as well. Below we add an ISBN node to the book node with ID 0001, which is as follows:

--Adding nodes

Set @xmlDoc. Modify (' Insert 78-596-134 before (/root/book[@id =0001]/price) [1] ')

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

Run the results as shown in figure:

4. Add and Remove Properties

When you learn to work with nodes, you will find that many times we need to manipulate the nodes. This time we still use the Modify method, for example, to add a date attribute to the book node with ID 0001 to store the publication time. The code is as follows:

--Adding properties

Set @xmlDoc. Modify (' Insert attribute date{' 2008-11-27 '} into (/root/book[@id = 0001]) [1] ')

Select @xmlDoc. Query (' (/root/book[@id = ' 0001]) ')

Run the results as shown in figure:

If you want to add multiple attributes to a node at the same time, you can use a collection of attributes to do this, and the set of attributes can be written as: (Attribute date{"2008-11-27"}, Attribute year{"2008"}), you can add more. Here is no longer an example.

5. Delete Attributes

To delete an attribute, such as deleting the id attribute of the book node with ID 0001, we can use the following code:

--Delete attribute

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

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

Run the results as shown in figure:

6, modify the property

Modifying property values is also common, such as modifying the id attribute of the book node with ID 0001 to 0005, and we can use the following code:

--Modifying properties

Set @xmlDoc. Modify (' Replace value of (root/book[@id = "0001"]/@id) [1] with "0005"

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

Run the results as shown in figure:

Through the above study, I believe you can use the XML type well in SQL, the following is what we did not mention, you can go to other places lookup: exist () method to determine whether the specified node exists, the return value is true or false; Nodes () method that converts a set of nodes returned by a query to a set of record rows in a table similar to the result set.