SQL Server – 为xml列返回xml子节点

发布时间：2020-12-12 06:31:07 所属栏目：MsSql教程来源：网络整理

导读：给定表T列： ID UNIQUEIDENTIFIERCreatedDate DATETIMEXmlData XML XmlData的结构如下： application details firstname="first" lastname="last" statementstatement/statement /details educationHistory education subject="subject1" / education subject

给定表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/*')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!