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!