Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Retrieving Multiple XML Rows from a single row with an XML Column Type in SQL Server

Overview

I’m attempting to pull rows from a table with an XML Field Type.  I’m having issues getting multiple values from a single row.

Setup

Run the following query to set up the table:

CREATE TABLE CourseData (


CourseId int IDENTITY(1,1) PRIMARY KEY,


XmlData XML


)


INSERT INTO CourseData VALUES


(


'<Course xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://someuri.local/CourseRecord.xsd">


  <SchemaVersion>1.1</SchemaVersion>


  <CourseData>


    <CourseCode>AAA999</CourseCode>


    <CampusList>


              <campus xmlns="http://someuri.local/campus.xsd">


                     <campusId>1</campusId>


              </campus>


              <campus xmlns="http://someuri.local/campus.xsd">


                     <campusId>2</campusId>


              </campus>


       </CampusList>


  </CourseData>


</Course>')


INSERT INTO CourseData VALUES


(


'<Course xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://someuri.local/CourseRecord.xsd">


  <SchemaVersion>1.1</SchemaVersion>


  <CourseData>


    <CourseCode>AAA123</CourseCode>


    <CampusList />


  </CourseData>


</Course>')


As you can see from the 2 XML values, the first has values in the CampusList node, the other does not.

Required Result

I need to select a list of all CourseId’s, and all CampusIds in the XML for that course, with NULL as the CampusID if there aren’t any CampusIDs.

CourseId    CampusId

----------- --------------


1           1

1           2

4           NULL

Attempt 1

I attempted to use the .nodes selector to grab the campusId value, then query them in the SELECT statement. Below, you can see I select all the way down into the Campus entity to retrieve the campus id.

SELECT CourseId, t.r.value('

                           declare namespace C="http://someuri.local/CourseRecord.xsd";

                           declare namespace CAM="http://someuri.local/campus.xsd";

                           (.)', 'varchar(50)') as CampusId

FROM

CourseData CROSS APPLY

XmlData.nodes('

                     declare namespace C="http://someuri.local/CourseRecord.xsd";

                     declare namespace CAM="http://someuri.local/campus.xsd";

                     /C:Course/C:CourseData/C:CampusList/CAM:campus/CAM:campusId') t(r)

Result

CourseId    CampusId

----------- ------------------

1           1

1           2

As you can see, I got the 2 values from the 1st course as I should have, but I’m not seeing anything from the 2nd course because the nodes selector selects nodes that don’t exist at all in the 2nd course.

Attempt 2

SELECT CourseId, t.r.value('

           declare namespace C="http://someuri.local/CourseRecord.xsd";

           declare namespace CAM="http://someuri.local/campus.xsd";

           (./CAM:campus/CAM:campusId)', 'varchar(50)') as CampusId

FROM

CourseData CROSS APPLY

XmlData.nodes('

                     declare namespace C="http://someuri.local/CourseRecord.xsd";

                     declare namespace CAM="http://someuri.local/campus.xsd";

                     /C:Course/C:CourseData/C:CampusList') t(r)


I attempted to make the nodes selector select more shallow, and only went down to the CampusList level, and tried to use xpath in the  value function to pull out the CampusId, but get the following error:

Result 1

Msg 2389, Level 16, State 1, Line 4

XQuery [CourseData.XmlData.value()]: ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’

I was able to find out that I can supply a selector on the end of the Value function to get values out: (look at highlighted value below)

SELECT CourseId, t.r.value('

                           declare namespace C="http://someuri.local/CourseRecord.xsd";

                           declare namespace CAM="http://someuri.local/campus.xsd";

                           (./CAM:campus/CAM:campusId)[1]', 'varchar(50)') as CampusId

FROM

CourseData CROSS APPLY

XmlData.nodes('

                     declare namespace C="http://someuri.local/CourseRecord.xsd";

                     declare namespace CAM="http://someuri.local/campus.xsd";

                     /C:Course/C:CourseData/C:CampusList') t(r)

This gets me the following:

Result 2

CourseId    CampusId

----------- -----------

1           1

2           NULL

This however is missing the 2nd value from the 1st record.  If I modify the query and put a 2 in the brackets, I get:

Result 3

CourseId    CampusId

----------- -----------------

1           2

2           NULL

If I put a 3 in, I get:

CourseId    CampusId

----------- ------------

1           NULL

2           NULL

Solution

The solution was OUTER APPLY.  CROSS APPLY works like an inner join against the primary table.  Using OUTER APPLY I got the CourseId and a null CampusId column when there was no data base on the nodes selector in the 2nd “table”:

SELECT CourseId, t.r.value('

                           declare namespace C="http://someuri.local/CourseRecord.xsd";

                           declare namespace CAM="http://someuri.local/campus.xsd";

                           (.)', 'varchar(50)') as CampusId

FROM

CourseData OUTER APPLY

XmlData.nodes('

                     declare namespace C="http://someuri.local/CourseRecord.xsd";

                     declare namespace CAM="http://someuri.local/campus.xsd";

                     /C:Course/C:CourseData/C:CampusList/CAM:campus/CAM:campusId') t(r)

Result

CourseId    CampusId

----------- --------------

1           1

1           2

2           NULL

Tags: SQL, SQL Server, T-SQL,

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\