Practical Introduction to XML Manipulation Using XQuery in SQL Server

Exists() Method

The Actors table does not have any meaningful
column data at this point, so to provide some visual indication of who the
record belongs to, we will now add a LastName column to the table, and then populate
it with data parsed from the ContactInfo XML field.  First, we’ll add the
column:

ALTER TABLE Actors ADD LastName VARCHAR(50)
GO

We want to update the new fields using each actors’
Stage name if they have one, and use their Legal name if not.  For this
scenario we can use the  exists() method.  The
exists() method takes a single XQuery expression string parameter, and simply
returns a 1 or 0 based on the existence of the values searched for.  For
example:

SELECT ContactInfo.exist('//Contact/Names/Name[@type="Stage"]/Last') AS StageLastNameExists, *
FROM Actors
GO

This returns:

So, we can combine the exists() method with the
query() method to get the outcome we are looking for.  An UPDATE that uses CASE
will also solve this:

UPDATE Actors
SET LastName = (
CASE --test for Stage LastName existence
WHEN ContactInfo.exist('//Contact/Names/Name[@type="Stage"]/Last') = 1
THEN –-if a Stage name exists, update with Stage LastName value
CONVERT(VARCHAR(50),ContactInfo.query('//Contact/Names/Name[@type="Stage"]/Last/text()'))
ELSE –-if a Stage name does not exist, update with Legal LastName value
CONVERT(VARCHAR(50),ContactInfo.query('//Contact/Names/Name[@type="Legal"]/Last/text()'))
END
) FROM Actors
GO

If a ‘Name’ node exists having a ‘type’ attribute of
‘Stage,’ then the exists() method returns 1, and the LastName field is updated
with the Stage name.  If no Stage name exists, as in the case of Nicole’s
record, the actor’s Legal name is used to update the field instead.  The contents
of the Actors table, after running the above statements, are as follows:

FLWOR expressions

If we want to return values from certain nodes, we can
obtain simple results using the expressions we have looked at so far.  However,
for more powerful iterative searches, we need to use href=”http://en.wikipedia.org/wiki/FLWOR”>FLWOR expressions.   The FLWOR
acronym stands for For, Let, Where, Order By, Return,
describing the operations involved in a FLWOR expression.  Let’s try an
example.  If we want to get a list of all of a particular actor’s names, both first
and last, we can use a FLWOR expression to iterate through all of the ‘Name’ nodes:

SELECT ContactInfo.query('for $Names in /Contact/Names
                           return
                             for $Name in $Names/Name
                               return
                                 concat("[",$Name/First[1]," ",$Name/Last[1],"] ")
                           ') AS Names
FROM Actors
WHERE ID = 1
GO

here, we used a simple for clause to iterate all ‘Name’
nodes in the ‘Names’ node for record 1.  Because we needed multiple nodes from
each ‘Name’ node (‘First’ and ‘Last’), we had to nest another for.  The
nested for iterates every node inside of each ‘Name’ node, finding the
values for ‘First’ and ‘Last.’  We then added the concat() function in order to
return a singleton value, made up of both first and last name values, formatted
with brackets and a space.
We can also use FLWOR expressions for finding
values where we have only partial information.  Let’s say we know the last 4
digits of one of Tom’s phone numbers, and we want to return the entire 9-digit
number.  We can implement the where clause to do this:

SELECT ContactInfo.query('for $Phones in /Contact/Phones
                           return
                             for $Phone in $Phones/Phone
                               where substring($Phone,7,4)="3399"
                                 return                                                                                   data($Phone)
                        ') AS Phone
FROM Actors
WHERE ID = 1
GO

Here, we used a substring() function that matches what
we know (that last 4 digits of the number) to a ‘Phone’ value.  Note that we’ve
used the data() function to strip the XML tags, similar to how the text()
method is used.  To include the phone type, we can do the following:

SELECT ContactInfo.query('for $Phones in /Contact/Phones
                           return
                             for $Phone in $Phones/Phone
                               where substring($Phone,7,4)="3399"
                                 return
                                   concat($Phone/@type,": ",$Phone)
                        ') AS Phone
FROM Actors
WHERE ID = 1
GO

As we showed before, the final results of any of
these queries can be converted to VARCHAR to remove the XML links.
A similar query shows how to locate all ‘Name’ node
values that begin with the letter ‘M’:

SELECT ContactInfo.query('for $Names in /Contact/Names
                           return
                             for $Name in $Names/Name
                               where substring($Name/Last[1],1,1)="M"
                                 return
                                  concat("[",$Name/First[1]," ",$Name/Last[1],"] ")
                        ') AS LegalName
FROM Actors
WHERE ID = 1
GO

Summary

XQuery expressions can be combined with joins and
integrated into almost any type of SQL statement.  We’ve looked at just a
few practical examples of XQuery method and expression implementations – there
is much more to learn, especially with FLWOR expressions.  I hope this
introduction has sparked your interest!]]>