Beyond Relational: XML Workshop V - Reading Values from XML Columns

Friday, November 30, 2007

XML Workshop V - Reading Values from XML Columns

If you are new to XQuery, you might find it little tricky to read values from an XML column. Reading a value from an XML variable is slightly different from reading values from an XML column. In an XML variable we have a single XML document. But the case of an XML column is different. When you query a table, each row in the table has an XML document stored in it.

Let us look at an example. In the following example, we assign a value to an XML variable and Query it.

    1 /*
    2 Let us declare an XML variable and store a value.
    3 */
    4 DECLARE @x XML
    5 SET @x = 
    6 '<Name>
    7     <First>Jacob</First>
    8     <Last>Sebastian</Last>
    9 </Name>'
   10 
   11 /*
   12 Query the XML variable.
   13 */
   14 SELECT
   15     n.l.value('First[1]','VARCHAR(20)') AS First,
   16     n.l.value('Last[1]','VARCHAR(20)') AS Last
   17 FROM @x.nodes('/Name') n(l)
   18 
   19 /*
   20 OUTPUT
   21 
   22 First                Last
   23 -------------------- --------------------
   24 Jacob                Sebastian
   25 
   26 (1 row(s) affected)
   27 */

That was pretty much simple. You will need a slightly modified version of this query, if you need to read the values from an XML column. We need to use CROSS APPLY so that we can access more than one record unlike the previous example, where we had only one variable.

    1 /*
    2 Let us create a table and insert an XML value.
    3 */
    4 CREATE TABLE Employees ( Emp XML )
    5 INSERT INTO Employees ( Emp )
    6 SELECT
    7 '<Name>
    8     <First>Jacob</First>
    9     <Last>Sebastian</Last>
   10 </Name>'
   11 
   12 /*
   13 Let us try to query the table now
   14 */
   15 SELECT
   16     n.l.value('First[1]','VARCHAR(20)') AS First,
   17     n.l.value('Last[1]','VARCHAR(20)') AS Last
   18 FROM Employees
   19 CROSS APPLY Emp.nodes('//Name') n(l)
   20 
   21 /*
   22 OUTPUT:
   23 
   24 First                Last
   25 -------------------- --------------------
   26 Jacob                Sebastian
   27 
   28 (1 row(s) affected)
   29 */

My XML Workshop V at SQLServerCentral presents a detailed tutorial which explains the usage of CROSS APPLY. It also presents a few different ways to query a table and retrieve different pieces of information.