SQL Server - returning xml child nodes for xml column

Question

Given table T with columns:

ID UNIQUEIDENTIFIER
CreatedDate DATETIME
XmlData XML

Where XmlData is structured like:

<application>
    <details firstname="first" lastname="last">
        <statement>statement</statement>
    </details>
    <educationHistory>
        <education subject="subject1" />
        <education subject="subject2" />
    </educationHistory>
    <experienceHistory>
        <examiningExperienceHistory>
            <examiningExperience module="module1" />
            <examiningExperience module="module2" />
        </examiningExperienceHistory>
        <teachingExperienceHistory>
            <teachingExperience module="module1" />
            <teachingExperience module="module2" />
        </teachingExperienceHistory>
    </experienceHistory>
</application>

I need to return an extract like so:

ID Date       FirstName LastName Education    ExaminingExp TeachingExp
-----------------------------------------------------------------------
1  02-10-2012 First     Last     <xmlextract> <xmlextract> <xmlextract>

So far I have:

SELECT ID,
       CreatedDate [Date],
       XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName],
       XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName]
FROM T

I'm struggling with thee last three columns. For each record, I need to list teaching/examining experience, and education. Can anybody help?

podiluska · Accepted Answer · 2012-10-02 13:00:59Z

11

Use .query to extract xml.

eg

select 
XmlData.query('/application/educationHistory/*'),
XmlData.query('/application/experienceHistory/examiningExperienceHistory/*'),
XmlData.query('/application/experienceHistory/teachingExperienceHistory/*')

answered Oct 2, 2012 at 13:00

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Paul Fleming Over a year ago

This is great, thanks. Just to be cheeky, is there a way I can restrict what is returned in the query? e.g. instead of return the full xml under the path, return only certain attributes. So given <education subject="subject1" grade="grade" />, return only <education subject="subject1" />,

podiluska Over a year ago

You can do xmldata.query ('data(/application/educationHistory/education/@subject)') which will get the value of the subject attribute

Jaime Over a year ago

@podiluska Not with query(), but with value().

Jaime Over a year ago

You can filter the nodes you want to be returned. I.e. @xmlVar.query('/application/educationHistory/education[@subject="subject1"]'), but you cannot filter the returned attributes. Every attribute in the returned nodes will be returned

Jaime Over a year ago

@podiluska True, sorry, I misunderstood your comment. This returns the values of that attributes in all the nodes under that XQuery path.

Jaime · Accepted Answer · 2012-10-02 13:06:30Z

5

Try this:

SELECT ID, Created [DATE] 
XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName],
XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName],
XmlData.query('/application/educationHistory'),
XmlData.query('/application/experienceHistory/examiningExperienceHistory'),
XmlData.query('/application/experienceHistory/teachingExperienceHistory')
FROM T

answered Oct 2, 2012 at 13:06

Jaime

1,1201 gold badge8 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL Server - returning xml child nodes for xml column

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related