XQuery is a language used to query XML data. You can
query and manipulate data from XML documents or data sources that can be
viewed by XML. There are four simple methods for querying XML data with
XQuery:
Query( )
Value( )
Exist( )
Nodes( )
The query() method is used to return XML data that matches a query.
In Figure 1, we will perform a query that returns everyone that has a favorite color.
Figure 1. Sample query() Method
DECLARE @xmlData XML
SET @xmlData = '<=xml version="1.0" encoding="UTF-8" standalone="yes"=>
<employee>
<person>
<name>
<FirstName>Addie</FirstName>
<LastName>Banning</LastName>
</name>
<Age>21</Age>
<FavoriteColor>Blue</FavoriteColor>
</person>
<person>
<name>
<FirstName>Bill</FirstName>
<LastName>Bergen</LastName>
</name>
<Age>99</Age>
<FavoriteColor>Green</FavoriteColor>
</person>
<person>
<name>
<FirstName>Jennifer</FirstName>
<LastName>Liddle</LastName>
</name>
<Age>9</Age>
<FavoriteColor>Pink</FavoriteColor>
</person>
</employee>'
-- Here we create the table to hold the XML data
CREATE TABLE #tbl_xml (id INT IDENTITY PRIMARY KEY, employee XML)
-- Here, we insert the XML data into the xml column of the table
INSERT INTO #tbl_xml(employee)
VALUES (@xmlData)
-- Here, we perform our query
SELECT employee.query(
'for $p in //employee
where $p//FavoriteColor
return
<employee>
<name>{$p//FirstName}</name>
</employee>
'
)
FROM #tbl_xml
DROP TABLE #tbl_xml
|
In Figure 1 we created a temporary table called #tbl_xml and inserted the XML data into that temporary table. The query shown in Figure 12.14 uses XQuery to SELECT
the information in the XML data type to list everyone that has a
favorite color. Let’s take a look at this query in more detail.
Figure 2. Query() Method In-depth
SELECT employee.query(
'for $p in //employee
where $p//FavoriteColor
return
<employee>
<name>{$p//FirstName}</name>
</employee>
'
)
FROM #tbl_xml
|
The first part of our
query, SELECT people.query, uses a standard SQL command, SELECT,
followed by the column name in our #tbl_xml document, people. We then
use the method, query(), to tell our SELECT
statement that we will be querying against this XML data column. After
that, we simple write out an XPath statement and close it with the FROM clause. XPath will be discussed in our next section. Table 1 includes a description of the XQuery argument.
Table 1. Query() Method Argument
| Query() Argument | Description |
|---|
| XQuery | Is a string, an XQuery expression that queries for XML nodes such as elements and attributes, in an XML instance. |
Warning
You need to observe
some basic syntax rules when you are writing your code. First, XQuery is
case-sensitive. Pay close attention to this. Second, XQuery elements
and attributes MUST BE valid XML names. Lastly, XQuery variables are
always defined with a $ followed by the variable name (example: $name).
The value() method allows you
to extract a value from the XML document. This will enable you to
compare XML data with data from non-XML columns. For example, in Figure 2, you can use the following query to return the age of ‘Bill Bergen’ to an integer.
Figure 2. Sample Value() Method
SELECT employee.value('/employee[1]/person[2]/Age[1][text()]', 'int')
AS Age FROM #tbl_xml
|
As
you can see, the value() method requires arguments. The first argument
is the XQuery expression, and the second argument is the SQL data type.
Use Table 2 as a guide for the value() parameter.
Table 2. Value() Method Argument
| Value() Argument | Description |
|---|
| XQUERY | Is
the XQuery expression, a string literal that retrieves data inside the
XML instance. The XQuery must return at most one value. Otherwise, an
error is returned. |
| SQLType | Is
the preferred SQL type, a string literal, to be returned. The return
type of this method matches the SQLType parameter. SQLType cannot be an
XML data type, a common language runtime (CLR) user-defined type, image,
text, ntext, or sql_variant data type. SQLType can be an SQL,
user-defined data type. |
The exist() method is used
to check the existence of a value in a XML document. This method will
return an integer value of 1 if the value returned is a non-null value
and a 0 integer for a NULL value. In Figure 12.16 we will query the sample XML document, shown in Figure 12.13, to see if the name ‘Jennifer’ exists. Table 3 includes a description of the Exist() argument.
Figure 3. Sample Exist() Method
SELECT pk, employee FROM #tbl_xml
WHERE employee.exist('/employee/person/name/FirstName[.="Jennifer"]') = 1
|
Table 3. Exist() Method Argument
| Exist() Arguments | Description |
|---|
| XQuery | Is an XQuery expression, a string literal. |
The
last method for querying XML documents in an XML data type is the
nodes() method. The nodes() method will return a rowset for each row in
the query. This is helpful when you want to make your XML data type into
a relational format. Every XML document has a node, and in the XML data
type this is the document node. The document node is the node at the
top of every XML data type. In Figure 12.17, we query the sample XML document, shown in Figure 4, to return rows for each person in our employee node. Table 4 includes a description of the Nodes() argument.
Figure 4. Sample Node() Method
SELECT T2.employee.query('.')
FROM #tbl_xml
CROSS APPLY employee.nodes('/employee/person') as T2(employee)
|
Table 4. Nodes() Method Argument
| Nodes() Argument | Description |
|---|
| XQuery | Is
a string literal, an XQuery expression. If the query expression
constructs nodes, these constructed nodes are exposed in the resulting
rowset. If the query expression results in an empty sequence, the rowset
will be empty. If the query expression statically results in a sequence
that contains atomic values instead of nodes, a static error is raised. |
| Table(Column) | Is the table name and the column name for the resulting rowset. |
SQL Server 2008 provides
extensions that allow XQuery to modify data using the modify() method.
The modify() method includes three DML statements: insert, delete, and
replace value of (see Table 5).
Table 5. Modify() Method Argument
| DML | Description |
|---|
| insert | Inserts
one or more nodes as a child. Insert allows you to insert XML before or
after and existing XML node. You can also insert attributes. |
| delete | Delete XML elements or attributes from your XML document |
| replace value of | Replace a node with a new value that you specify. The node you select must be a single node, not multiple nodes. |
In Figure 5 we use our XML document, shown in Figure 1, to perform and insert, delete, and replace value of. Figure 12.18 will insert the tag <HireDate></HireDate> for employee number 3, Jennifer. Notice that we use the UPDATE statement to perform this function and not the INSERT statement. The insert is performed in the XQuery command.
Figure 5. Insert DML for Modify() Method
UPDATE #tbl_xml SET employee.modify(
'insert <HireDate>5/5/1999</HireDate>
as last into (/employee/person[3])[1]')
where pk=1
|
In Figure 6 we will update employee number three’s age from 9 to 10 using the replace value of DML.
Figure 6. Replace Value of DML for Modify() Method
UPDATE #tbl_xml
SET employee.modify(
'replace value of
(/employee/person[3]/Age[1]/text())[1]
with "10"'
)
where pk=1
go
|
In Figure 7 we will use the delete DML. Here we will delete employee number three’s HireDate.
Figure 7. Using the Delete DML
UPDATE #tbl_xml
SET employee.modify(
'delete /employee/person[3]/HireDate')
where pk=1
go
|
XPath
XPath (XML Path
Language) is a query language used to identify a set of nodes within an
XML document. XPath can be used to compute values such as strings,
numbers, or Boolean values from an XML document. It supports many
different expression types. Table 6 describes each expression type.
Table 6. Expression Types for XPath
| Category | Function | Description |
|---|
| Numeric | Ceiling | Returns the smallest integer of the values passed |
| | Floor | Returns the largest integer of the values passed |
| | Round | Rounds to the nearest integer |
| Boolean | Not | True or False value |
| String | Concat | Concatenates the strings passed |
| | Contains | Returns a true value if the first argument contains the second argument |
| | Substring | Returns a portion of the first argument starting at the location of the second argument |
| | String-length | Returns the length of a string passed |
| Node | Number | Returns a number for the value of the node passed |
| Context | Position | Returns an integer that is the current position in the sequence. |
| | Last | Returns an integer that is the count of the last item in the sequence |
| Sequences | Empty | Returns true if the argument passed, which is a sequence, is empty. |
| | Distinctvalues | Removes duplicates from your sequence |
| Aggregate | Avg | Returns the average of a set of numbers |
| | Count | Counts the number of items in the set and returns an integer |
| | Min | Returns the minimum value from a set of numbers |
| | Max | Returns the maximum value from a set of numbers |
| | Sum | Returns the sum of a set of numbers. |
| Constructor | Various | Allows you to create an XSD type from another type or literal |
| DataAccess | Data | Returns the typed value of the node |
| | String | Returns the value of the argument as a string |
Now, let’s explore some examples of how SQL Server 2008 uses XPath to calculate numbers, strings, and Boolean values. In Figure 8, we have created an XML document and stored it in our XML data type in the declared table @XMLTable.
Figure 8. Sample XML Document
DECLARE @xmlData XML
SET @xmlData = '<?=xml version="1.0" encoding="UTF-8" standalone="yes"=?>
<product>
<item>
<name>BaseBall Gloves</name>
<tagid>52487-1</tagid>
<quantity>10</quantity>
</item>
<item>
<name>BaseBall Bats</name>
<tagid>52487-1</tagid>
<quantity>15</quantity>
</item>
<item>
<name>BaseBall Balls</name>
<tagid>94235-1</tagid>
<quantity>4</quantity>
</item>
</product>'
-- Create a new table
declare @XMLTable table (pk INT IDENTITY PRIMARY KEY, colxml XML)
--Insert data into the new table
INSERT INTO @XMLTable(colxml)
VALUES (@xmlData)
|
In Figure 9 we will use the XPath function, count, in order to count the number of item nodes in our XML document.
Figure 9. XPath Expression count
-- Count the number of people
SELECT colxml.query(
'count(//item)
')
FROM @XMLTable
|
In Figure 10 we will use the XPath function, contains, to return the string that contains the word “BaseBall Bats.”
Figure 10. XPath Expression Contains
SELECT *
FROM @XMLTable
WHERE
colxml.exist('/product/item/name[contains(.,"BaseBall Bats")]') = 1
|
These are just two examples of all the XPath expressions you can use.
