SQLUNINTERRUPTED

Continuing with the discussions on XML, today in this post we will talk about how to use XQuery with XML in SQL Server 2005 and later. For a list of other related topics refer here.

With XML data type support in SQL Server 2005, documents can now be stored in the form of XML and then Queries using XQuery. XQuery Language can query both structured or semi-structured XML data. XQuery is based on XPath queries, but provides better Iteration, better sorting ability.

A list of XQuery functions which can be used with the XML data types in SQL can be found at the following MSDN site.

Similarly some of the XQuery() operators supported with XML data types are

1. Numeric operators (+, -, *, div, mod)
2. Operators for value comparison (eq, ne, lt, gt, le, ge)
3. Operators for general comparison ( =, !=, <, >, <=, >= )

To better understand the usage, lets try to use some of these functions and operators in a query. To use XQuery with SQL XML data type, we would need to call the XML Query() method. We will be discussing some other XML data types methods in subsequent posts.

Query method takes an XML XQuery expression and returns an Untyped XML as an output. An XQuery expression has two main parts

1) A XQuery Prolog, which declares the namespaces to be used for typed XML. In SQL the prolog can only contain namespace declarations.
2) The XQuery body, which declares a sequence of XQuery expression, for the intended results.

SELECT Instructions.query(‘
declare namespace
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
    /AWMI:root/AWMI:Location[@LocationID=10]’) AS Result
FROM  Production.ProductModel
WHERE ProductModelID=7

In the above query, the DECLARE NAMESPACE part is used to declare a prefix AWMI for the XML schema ProductModelManuInstructions. This prefix will be used when referencing any element in the XML document created against this schema.

The “/AWMI:root/AWMI:Location[@LocationID=10]” expression is the XQuery body and is used to iterate to the desired element or node or attribute in the XML instance.

Now lets take a slightly complex example, which would include some of the XQuery functions and operators mentioned earlier.

SELECT
Resume.query(‘
        declare namespace rns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
    for $Name in /rns:Resume/rns:Name
        return
        <Name>
            {string($Name/rns:Name.Prefix)}{string(" ")}{string($Name/rns:Name.First)}{string(" ")}
            {string($Name/rns:Name.Middle)}{string(" ")}{string($Name/rns:Name.Last)}{string(" ")}
        </Name>,
    <Skill>
        {substring( ((rns:Resume/rns:Skills)[1]),1,56)}
    </Skill>,
    for $Employment in /rns:Resume/rns:Employment
        return
        <JobDetails>
                StartDate = {string($Employment/rns:Emp.StartDate)[1]}
                EndDate = {string($Employment/rns:Emp.EndDate)[1]}
                JobProfile = {concat(string(($Employment/rns:Emp.OrgName)[1]),string("—"),
                string(($Employment/rns:Emp.JobTitle)[1]))}
        </JobDetails>
        ‘
) as Results
from
HumanResources.JobCandidate where EmployeeID = 268

Lets take a minute and understand the query first. As mentioned there are two parts to the XQuery, the Prolog and the Body.  The Prolog contains the namespace declaration, which in this case is
    declare namespace rns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

The Body is a comma separated list of XQuery expressions to return the desired results. In this case the body contains 3 XQuery Expressions, described below

The First Expression, creates a NAME string from the Prefix, First, Middle and Last elements in the XML. Note the usage of string functions and the FOR loop. We will discussing this special Looping mechanism in subsequent posts.
    for $Name in /rns:Resume/rns:Name
    return
    <Name>
        {string($Name/rns:Name.Prefix)}{string(" ")}{string($Name/rns:Name.First)}{string(" ")}
        {string($Name/rns:Name.Middle)}{string(" ")}{string($Name/rns:Name.Last)}{string(" ")}
    </Name>,
The Second expressions returns the skill set for the Candidate. Note the usage of substring function in the expression.
    <Skill>
        {substring( ((rns:Resume/rns:Skills)[1]),1,56)}
    </Skill>,
The third expressions, lists all the previous jobs undertaken by the Candidate. Note the usage of the For looping mechanism and the CONCAT function being used.
    for $Employment in /rns:Resume/rns:Employment
    return
    <JobDetails>
            StartDate = {string($Employment/rns:Emp.StartDate)[1]}
            EndDate = {string($Employment/rns:Emp.EndDate)[1]}
            JobProfile = {concat(string(($Employment/rns:Emp.OrgName)[1]),string("—"),
            string(($Employment/rns:Emp.JobTitle)[1]))}
    </JobDetails>

In SQL, the schema declaration can be separated from the XQuery using the WITH NAMESPACE statement. For example the above query can also be written as

WITH XMLNAMESPACES (‘http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume’ as rns)
SELECT Resume.query(‘
for $Name in /rns:Resume/rns:Name
return
……