SQL Server - returning xml child nodes for xml column
37,376
Solution 1
Use .query
to extract xml.
eg
select
XmlData.query('/application/educationHistory/*'),
XmlData.query('/application/experienceHistory/examiningExperienceHistory/*'),
XmlData.query('/application/experienceHistory/teachingExperienceHistory/*')
Solution 2
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
Author by
Paul Fleming
LinkedIn Profile Careers Page http://careers.stackoverflow.com/paulfleming Scrum Alliance Member Profile https://www.scrumalliance.org/community/profile/pfleming3 Microsoft Certification Transcript View Transcript Transcript ID: 939804 Access Code: pftransac
Updated on July 15, 2022Comments
-
Paul Fleming almost 2 years
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?
-
Paul Fleming over 11 yearsThis 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 11 yearsYou can do
xmldata.query ('data(/application/educationHistory/education/@subject)')
which will get the value of the subject attribute -
Jaime over 11 years@podiluska Not with query(), but with value().
-
Jaime over 11 yearsYou 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 11 years@podiluska True, sorry, I misunderstood your comment. This returns the values of that attributes in all the nodes under that XQuery path.