给定表T列：
ID UNIQUEIDENTIFIER CreatedDate DATETIME XmlData XML
XmlData的结构如下：
<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>
我需要像这样返回一个提取物：
ID Date FirstName LastName Education ExaminingExp TeachingExp ----------------------------------------------------------------------- 1 02-10-2012 First Last <xmlextract> <xmlextract> <xmlextract>
到目前为止我有：
SELECT ID,CreatedDate [Date],XmlData.value('(application/details/@firstname)[1]','varchar(max)') [FirstName],XmlData.value('(application/details/@lastname)[1]','varchar(max)') [LastName]
FROM T
我在最后三列中苦苦挣扎.对于每个记录,我需要列出教学/考试经验和教育.有人可以帮忙吗？
解决方法
使用.query来提取xml.
例如
select
XmlData.query('/application/educationHistory/*'),XmlData.query('/application/experienceHistory/examiningExperienceHistory/*'),XmlData.query('/application/experienceHistory/teachingExperienceHistory/*')
