SQL Server - 返回XML列的XML子节点

Question

给定表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

我在最后三列方面遇到了困难。对于每个记录，我需要列出教学/考试经验和教育背景。有人能帮忙吗？

- Paul Fleming

Answer 1

使用.query来提取xml。

例如：

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

- podiluska

---

太好了，谢谢。只是想问一下，有没有办法限制查询返回的内容？例如，不返回路径下的完整xml，而只返回特定属性。因此，对于 <education subject="subject1" grade="grade" />，只返回 <education subject="subject1" />。 - Paul Fleming

您可以执行 xmldata.query('data(/application/educationHistory/education/@subject)')，这将获取学科属性的值。 - podiluska

@podiluska 不是用query()，而是用value()。 - Jaime

您可以过滤要返回的节点，例如：@xmlVar.query('/application/educationHistory/education[@subject="subject1"]')。但是您不能过滤返回的属性。返回节点中的每个属性都将被返回。 - Jaime

@podiluska 确实，抱歉，我误解了你的评论。这将返回该XQuery路径下所有节点中该属性的值。 - Jaime

Answer 2

试试这个：

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

- Jaime