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

我正在努力处理最后三列。对于每条记录，我需要列出教学/考试经历和教育。有人可以帮忙吗？

Answer 1

使用.query提取xml。

例如

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