The use of XML in SQL Server

The core of SQL Server support for XML lies in 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 name of the type as if you were specifying a type in SQL Server.

The data type of XML ensures that your XML data is preserved in good condition and conforms to ISO standards. Before defining an XML data type, we first need to know several of its limitations, as follows:

• An instance of an XML column cannot contain more than 2GB of data.
• An XML column cannot be an index.
• An XML object cannot be used in a GROUP BY clause.
• The data type of XML does not support comparison and sorting.

Defining an XML variable

DECLARE @ClientList xmlset @ClientList = ' <?xml version= "1.0" encoding= "UTF-8"?><!--A List of current clients--& Gt;<people><person id= "1234" ><firstname>john</firstname><lastname>doe</ Lastname></person><person id= "5678" ><firstname>jane</firstname><lastname>doe </LastName></Person></People> ' SELECT @ClientListGO

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 that contains the XML is called after the variable name.

I set the value of the variable and then use Select to retrieve the value. As we thought, it returned 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>

Now let's look at how to define a column of XML

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

Use adventureworks2008r2goif object_id (' dbo. Storeclients ') is not nulldrop TABLE dbo. Storeclientsgocreate TABLE dbo. Storeclients (StoreID INT IDENTITY PRIMARY key,clientinfo XML not NULL) GO

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

DECLARE @ClientList xmlset @ClientList = ' <?xml version= "1.0" encoding= "UTF-8"?><!--A List of current clients--& Gt;<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 mentioned above, both creation and insertion are straightforward, so let's look at how to create an XML parameter

Defining an XML parameter

For example, I define @StoreClients as an input parameter, and configure it as an XML type

Use adventureworks2008r2goif object_id (' dbo. Addclientinfo ', ' P ') is not nulldrop PROCEDURE dbo. Addclientinfogocreate PROCEDURE dbo. Addclientinfo@storeclients Xmlasinsert into dbo. Storeclients (Clientinfo) VALUES (@StoreClients) GO

Then let's look at how XML is used as a parameter in a stored procedure:

DECLARE @ClientList xmlset @ClientList = ' <?xml version= "1.0" encoding= "UTF-8"?><!--A List of current clients--& Gt;<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 very straightforward, first assign the XML data to the variable, and then execute the variable as a parameter to the SP, which is the query you will find that the data is already in the table.

Now let's take a look at the methods supported by XML types: query( )value().

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

Attention:

because XQuery is a very complex language, we are only involved in part of his component, and if you want to further understand how it is applied, see MSDN XQuery Language Reference .

Well, let's start with an example. The query () and value two methods use XML data. It is important to note that my next test environment is SQLServer2008 R2. The instance contains the ClientDB database, ClientInfoCollection 的 XML data, and ClientInfo tables.

Use master; GOif db_id (' Clientdb ') is not nulldrop DATABASE clientdb; Gocreate DATABASE Clientdb; Gouse Clientdb; GOif object_id (' clientinfocollection ') is not nulldrop XML SCHEMA COLLECTION clientinfocollection; Gocreate 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" mi noccurs= "1" maxoccurs= "unbounded" > <xsd:complexType> <xsd:sequence> <xs D: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 "M         inoccurs= "0" maxoccurs= "5"/> </xsd:sequence>   <xsd:attribute name= "id" type= "xsd:integer" use= "required"/> </xsd:complexType> </xsd:el ement> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema> '; GOif object_id (' Clientinfo ') is not nulldrop TABLE clientinfo; Gocreate TABLE clientinfo (ClientID INT PRIMARY KEY IDENTITY, info_untyped XML, info_typed XML (clientinfocollection));    Nsert into Clientinfo (info_untyped, info_typed) VALUES (' <?xml version= ' 1.0 ' encoding= ' UTF-8 '?> <People> <person id= "1234" > <FirstName>John</FirstName> <LastName>Doe</LastName> </pe rson> <person id= "5678" > <FirstName>Jane</FirstName> <lastname>doe</lastname&gt    ; </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 is typically used to return an untyped XML instance that specifies a subset of XML, as follows, using parentheses with single quotation marks to implement an expression, syntax:

db_object.query(‘xquery_exp‘)

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

SELECT info_untyped.query ('/people ') as  people_untypedfrom Clientinfo;

Listing 2: Use query( ) to get<People>元素中的值

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

<People>  <person id= "1234" >    <FirstName>John</FirstName>    <LastName> doe</lastname>  </Person>  <person id= "5678" >    <firstname>jane</ firstname>    <LastName>Doe</LastName>  </Person></People>

Listing 3: /People content returned by the result set

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

Select Info_typed.query (  ' declare namespace ns= "Urn:clientinfonamespace";  /ns:people ') as People_typedfrom clientinfo;

Listing 4: Use query( ) to retrieve typed XML columns, and then you run this statement and you get results such as 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 column contains the namespaces involved.

If we are going to get the contents of the sub-level sub-element, we need to modify the expression by adding it /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_typedfrom 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: This result set is the result of non-typed data

<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're going to get the designated<Person>下面的某一个元素，需要加入涉及的id属性。下面对比类型和非类型的两种情况下指定元素属性时如何获取。

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_typedfrom clientinfo;

Listing 9: Retrieving data, specifying elements

There is no change in the previous, add the expression according to the element, and then use the brackets to add the @id value in the brackets, the result is as follows

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

Listing: ID 1234 Untyped data result return value.

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

<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 of 5678

Further display 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_typedfrom clientinfo;

Results

<FirstName>John</FirstName>

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

Listing 14: Display of the result 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_typedfrom 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, many times when you want to retrieve a particular element or attribute, instead of getting the XML element, you can use value (). This method only returns a specific value, not as a data type. Therefore, be sure to pass the 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_typedfrom clientinfo;

Listing 16: Retrieving <FirstName> the value

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

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

Listing 17:<FirstName>的两个结果

Of course, we can also retrieve the property value for each instance ID, and specify the int type to return.

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_typedfrom clientinfo;

Listing 19: Retrieving the id attribute value of two instances

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

Listing 20: Returns the properties of two IDs

In addition to defining your XQuery expression in an expression, you can also aggregate functionality to further define your query and operational data. For example, the Count () feature allows us to get <Person> the number of 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_typedfrom clientinfo;

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

The results are as follows:

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

Listing 22: <Person> number of elements in each column of data

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

Select   info_untyped.value (    ' concat ((/people/person/firstname) [2], "",       (/people/person/lastname) [ 2]) ',     ' varchar (+) ') as Fullnamefrom clientinfo;

Listing 23: Usingconcat()来连接数值

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

Listing 24: Return value after connection

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

Summarize

We basically understand the simple application of XML in SQL Server, from definition to usage. You also see a subset of query () retrieval, and you can use value () to retrieve the values of independent element attributes. Of course, in addition to and exist( ) nodes() such methods, with grammar are applied, this part is no longer talk about, similar. If you don't understand, you can talk privately. For more use, visit MSDN to get (search for XQuery Language Reference).

The use of XML in SQL Server