SQL Server XQuery
I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel
table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.
You can query a sequence with xquery as follows:
DECLARE @x xml; SET @x = N''; SELECT @x.query('(1,2,(10,11,12,13,14,15)),-6'); |
It returns:
1 2 10 11 12 13 14 15 -6 |
You can query a sequence with an xquery FLOWR
statement. FLOWR
stands for: FOR
, LET
, ORDER BY
, WHERE
, and RETURN
. A sample xquery with a FLOWER
statement is:
DECLARE @x xml; SET @x = N''; SELECT @x.query('for $i in ((1,2,(10,11,12,13,14,15)),-6) order by $i return $i'); |
It returns:
-6 1 2 10 11 12 13 14 15 |
You can query the entire node tree with the following xquery statement because it looks for the occurrence of any node with the /*
search string:
DECLARE @x xml; SET @x = N'<marvel> <avenger_name>Captain America</avenger_name> </marvel>'; SELECT @x.query('/*'); |
You can query the avenger_name
elements from the marvel_xml
table with the following syntax:
SELECT xml_table.query('/marvel/avenger_name') FROM marvel_xml; |
It returns the following set of avenger_name
elements:
<avenger_name>Hulk</avenger_name> <avenger_name>Iron Man</avenger_name> <avenger_name>Black Widow</avenger_name> <avenger_name>Thor</avenger_name> <avenger_name>Captain America</avenger_name> <avenger_name>Hawkeye</avenger_name> <avenger_name>Winter Soldier</avenger_name> <avenger_name>Iron Patriot</avenger_name> |
You can query the fourth avenger_name
element from the marvel_xml
table with the following xquery statement:
SELECT xml_table.query('/marvel[4]/avenger_name') FROM marvel_xml; |
It returns the following avenger_name
element:
<avenger_name>Thor</avenger_name> |
You can use the value()
function to verify an avenger_name
element exists:
SELECT CASE WHEN xml_table.value('(/marvel[4]/avenger_name)','nvarchar') = 'T' THEN 'Exists' END AS "Verified" FROM marvel_xml WHERE id = 3; |
The query returns the Exists
string when it finds a valid avenger_name
element. You have a number of other tools to query results sets from the XML node tree.
I hope this helps my students and anybody interested in writing xquery-enable queries.