Decomposing XML data with XQuery in SQL Server

As an example of how to decompose the data in the previous article, we first look at XQuery and the functionality it provides to developers in SQL Server 2005.

About XQuery

XQuery, also known as XML Query, is a language that queries XML data, allowing you to extract the required nodes and elements. It is defined by the consortium and can be used in most of today's mainstream database engines, such as Oracle, DB2, and SQL Server.

SQL Server the???? XQuery function

The following four functions are the XQuery functions in SQL Server 2005. (Note that XML, XQuery statements, and the following functions are case-sensitive.) For example, the SQL compiler accepts. exist in XML data, but rejects. exist or. exist. )

Xml.exist

This method returns a Boolean value based on the search expression on an XML node. For example, the statements in the XML snippet in List A will return 1 (true):

SELECT @x.exist ('/christmaslist/person[@gift = ' socks '] ')

This statement returns 0 (false):

SELECT @x. Exist ('/christmaslist/zach ')

Because the word "Socks" is enveloped, this statement will return 0 (false).

SELECT @x.exist ('/christmaslist/person[@gift = ' socks '] ')

Xml.value

This method accepts an XQuery statement and returns a single value. Using the same XML snippet in List A, you cannot use the value function to generate a "Betty" value, as follows:

SELECT @x.value ('/christmaslist[1]/person[1]/@name ', ' VARCHAR (20) ')

And XQuery generates "Zach" values.

SELECT @x.value ('/christmaslist[1]/person[2]/@name ', ' VARCHAR (20) ')

Xml.query

This method accepts an XQuery and returns an instance of an XML data type. These queries can be simple or complicated as needed, and here's a simple example:

SELECT @x.query ('/christmaslist/person ')

It returns an XML file:

The following is a reference fragment:

Xml.nodes

This method is useful when you need to decompose the data in an XML data type variable into relational data. This method takes an XQuery statement as an argument and returns a rowset that contains logical scalar data for the XML variable. The query in List B leverages the XML variables defined above and decomposes the data into a result set that displays the names of the people defined in the XML variable.

modifying OPENXML stored Procedures

Let me now explain how to modify the OpenXML stored procedure last week so that it can apply XQuery functionality. First, I load some data into an XML variable. As shown in Listing C. We can create a process that accepts XML parameters and then apply the XQuery function to insert the data from the XML file into a table without having to apply OPENXML. As shown in List D.

The initial application of XML in the database seems to be difficult to handle, and it takes some time to get used to using XQuery and XPath queries. However, after a period of study, you will find that the application of XML data in the database is quite practical.

For example, when you apply XML data to the stored procedures described above, you only need to call the database once rather than call the n-th database like typical stored procedure programming. There seems to be little difference between the two, but for a busy system, applying XML data can be a big benefit. Also, applying XQuery rather than OPENXML can significantly improve performance, especially for small XML files.