Given table T with columns:
\[code\]ID UNIQUEIDENTIFIERCreatedDate DATETIMEXmlData XML\[/code\]Where XmlData is structured like:\[code\]<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>\[/code\]I need to return an extract like so:\[code\]ID Date FirstName LastName Education ExaminingExp TeachingExp-----------------------------------------------------------------------1 02-10-2012 First Last <xmlextract> <xmlextract> <xmlextract>\[/code\]So far I have:\[code\]SELECT ID, CreatedDate [Date], XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName], XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName]FROM T\[/code\]I'm struggling with thee last three columns. For each record, I need to list teaching/examining experience, and education. Can anybody help?
\[code\]ID UNIQUEIDENTIFIERCreatedDate DATETIMEXmlData XML\[/code\]Where XmlData is structured like:\[code\]<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>\[/code\]I need to return an extract like so:\[code\]ID Date FirstName LastName Education ExaminingExp TeachingExp-----------------------------------------------------------------------1 02-10-2012 First Last <xmlextract> <xmlextract> <xmlextract>\[/code\]So far I have:\[code\]SELECT ID, CreatedDate [Date], XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName], XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName]FROM T\[/code\]I'm struggling with thee last three columns. For each record, I need to list teaching/examining experience, and education. Can anybody help?