Practical Introduction to XML Manipulation Using XQuery in SQL Server

W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).  XQuery uses XPath methods and expressions to search and retrieve XML data.  In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample XML data.

CREATE TABLE Actors (ID INT IDENTITY, ContactInfo XML)
GO

Now let’s insert a record containing some XML code.

INSERT INTO Actors VALUES('
       <Contact>
              <Names>
                      <Name type="Legal">
                              <First>Thomas</First>
                              <Middle>Cruise</Middle>
                              <Last>Mapother</Last>
                      </Name>
                      <Name type="Stage">
                              <First>Tom</First>
                              <Middle></Middle>
                              <Last>Cruise</Last>
                      </Name>
              </Names>
              <Addresses>
                      <Address type="Primary">
                              <Street>12345 Main Street</Street>
                              <City>San Diego</City>
                              <State>CA</State>
                              <Zip>92130</Zip>
                      </Address>
                      <Address type="Other">
                              <Street>6200 Cruise Avenue</Street>
                              <City>San Fernando</City>
                              <State>CA</State>
                              <Zip>92126</Zip>
                      </Address>
              </Addresses>
              <Phones>
                      <Phone type="Mobile">8085554422</Phone>
                      <Phone type="Home">8085553399</Phone>
              </Phones>
       </Contact>
       ')
GO

Let’s add another record:

INSERT INTO Actors VALUES('
              <Contact>
                    <Names>
                           <Name type="Legal">
                                 <First>Nicole</First>
                                 <Middle>Mary</Middle>
                                 <Last>Kidman</Last>
                           </Name>
                    </Names>
                    <Addresses>
                           <Address type="Primary">
                                 <Street>1001 Oak Avenue</Street>
                                 <City>San Diego</City>
                                 <State>CA</State>
                                 <Zip>92130</Zip>
                           </Address>
                           <Address type="Other">
                                 <Street>555 Main Street</Street>
                                 <City>San Fernando</City>
                                 <State>CA</State>
                                 <Zip>92126</Zip>
                           </Address>
                    </Addresses>
                    <Phones>
                           <Phone type="Mobile">8085554400</Phone>
                           <Phone type="Home">8085553300</Phone>
                    </Phones>
              </Contact>
       ')
GO

A quick  SELECT * FROM Actors now yields the following results:

Our XML column contains untyped XML data.  Typed XML uses XML schemata and server validation of the stored XML data’s structure and integrity – a good idea, but it adds a certain level of complexity to the storage, manipulation, and extraction of XML data.  We will use untyped XML for our examples.

Value() Method

The first method we’ll look at is the value() method.  This method requires two parameters: an XQuery string, and a data type to deposit the results of the query into.  Let’s try an example:

SELECT ContactInfo.value('(/Contact/Names/Name)[1]',
'varchar(50)') AS Name FROM Actors
 GO

This statement utilizes the value() method, specifying that the values in the first ‘Name’ node of every ‘Names’ group of nodes in each ‘Contact’ node be retrieved.  The ‘[1]’ is required to explicitly request that a singleton be returned (a scalar value).  The results are:

As you can see, the singleton result is the concatenation of the node values that are under the first ‘Name’ node group (the collection of First, Middle, and Last Name nodes).  Note that the concatenated value ‘TomCruise’ was not in the results, as those node values were in the second ‘Name’ node. To see the results from the second ‘Name’ node, run the following:

SELECT ContactInfo.value('(/Contact/Names/Name)[2]',
'varchar(50)') AS Name FROM Actors
GO

The results are:

Since Nicole’s XML data has only one ‘Name’ node, a NULL record is returned for her record in the Actors table. To retreive individual node values within the ‘Name’ nodes, run a query similar to:

SELECT ContactInfo.value('(/Contact/Names/Name/First)[1]', 'varchar(50)') AS FirstName,
       ContactInfo.value('(/Contact/Names/Name/Middle)[1]', 'varchar(50)') AS MiddleName,
       ContactInfo.value('(/Contact/Names/Name/Last)[1]', 'varchar(50)') AS LastName
FROM Actors
GO

Which returns:

Query() Method

What if we want to pick which name we retreive, based on the type?  This is where the query() method can be used.  The query() method requires only one parameter, an XQuery string.  Let’s run the following statement to retreive all of Tom’s last names by type :

SELECT ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last') AS LegalLastName,
       ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last') AS StageLastName
FROM Actors
WHERE ID = 1
GO

This time, we focus on the element attribute of ‘type,’ rather than child elements.  The query() method takes search parameters, beginning with ‘@’, followed by the name of the element attribute.  Running the above statement returns the following results (note that we specified ID = 1, which says that we only want to look at the ContactInfo field associated with Tom, not Nicole):

Text() Method

You’ll also notice that the query() method returns values in the form of XML nodes. To parse out the actual data, and convert the results to a string , we can run the following query:

SELECT CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Legal"]/Last/text()')) AS LegalLastName,
CONVERT(VARCHAR(50),ContactInfo.query('/Contact/Names/Name[@type="Stage"]/Last/text()')) AS StageLastName
FROM Actors
WHERE ID = 1
GO

The text() XQuery method parses values out from XML tags.