给定表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
我在最后三列方面遇到了困难。对于每个记录,我需要列出教学/考试经验和教育背景。有人能帮忙吗?
<education subject="subject1" grade="grade" />
,只返回<education subject="subject1" />
。 - Paul Flemingxmldata.query('data(/application/educationHistory/education/@subject)')
,这将获取学科属性的值。 - podiluska@xmlVar.query('/application/educationHistory/education[@subject="subject1"]')
。但是您不能过滤返回的属性。返回节点中的每个属性都将被返回。 - Jaime