Using XQuery

XQuery in SQL Server was introduced in version 2005, along with so many major advancements. The ability to store and query XML has changed the game of SQL Server and has made it what it more of what it should be, a better workflow / storage engine.

There are some things to consider prior to introducing XML into the database. The most plausible question you can ask yourself when using XML instead of a table is, “do these XML elements have any use as individual items, or do they only have meaning within the context of this XML document”. Weighing that question, along with the ease of XML portability will hopefully give you the answer you need.

Now to use XQuery. SQL Server only supports a subset of XQuery so you don’t want to get too fancy with your needs. There are certain functions (like substring) that are not supported, however most of the missing functionality can be addressed using SQL Server functions. If you do need these added functionalities, then you also may want to consider whether the XML manipulation using SQL is the right approach. While XML is quite useful, and while SQL Server can (architecturally) be used as a workflow engine, when it comes to certain items like display functions, SQL Server is not the right choice.

This following example is from a rules engine I am creating. The XML itself represents a rule, and the XQuery below parses the rule into a table. The beauty of the XML in this case is the extensibility and the portability. Meaning, I do not have to define all the elements if I don’t want to, or, I could add additional elements with ease. Then they are also easy to transfer because XML is a relational model in itself.

First, here is the XML:
[cc lang=”xml”]




35


truck


wife
child


receptionist
sales




[/cc]

Now how to process it:
[cc lang=”sql”]
DECLARE @RuleXML XML

SET @RuleXML =
‘



35


truck


wife
child


receptionist
sales



‘

SELECT
Attribute = N.c.value(‘(../@attribute)’, ‘nvarchar(4000)’)
,Value = N.c.value(‘(.)’, ‘nvarchar(4000)’)
,Operator = N.c.value(‘(../@operator)’, ‘nvarchar(4000)’)
FROM @RuleXML.nodes(‘/rule/conditions/filter/condition/value’) AS N(c)
WHERE N.c.value(‘(.)’, ‘nvarchar(4000)’) != ”
[/cc]
Please note, the value function above should be all lower case.

Running this self contained example, we see that we return a table of Attribute, Values, and Operators. These would then be used in order to further output a result set for the rule.

The most important line above is the FROM clause. It defines the path for the actual values we are returning. These values can have multiple rows of output, and in order to return these multiple rows, you need this line to drill down to the most granular element. From there, look at the select list. The select list contains operators that traverse the path upwards. You’ll notice this with the ../ which is the same as saying “go back one directory”. From here you can return the attributes by precluding them with the at (@) symbol.