从SQL Server中的XML读取子节点及其父属性

Jasqlg 发表于 Dev

Jasqlg

我有一个类似于此结构的XML。

<Root>
<id>a2bh5</id>
<Student ID="123">
    <Name>abc<Name>
    <course>ETL<course>
    <Scores>
        <Score>
            <Subject>SSIS<subject>
            <grade>B<grade>
        </score>
        <Score>
            <Subject>Informatica<subject>
            <grade>C<grade>
        </score>
    </Scores>
</Student>
<Student ID="456">
    <Name>xyz<Name>
    <course>ETL<course>
    <Scores>
        <Score>
            <Subject>Pentaho<subject>
            <grade>F<grade>
        </score>
        <Score>
            <Subject>Datastage<subject>
            <grade>A<grade>
        </score>
    </Scores>
</Student>
</Root>

我想在SQL Server中使用Xquery从子节点（分数）及其父属性（Id）中获取详细信息。

对于所有主题，期望查询结果如下。请帮忙。

Student_Id subject grade
========================
123     SSIS        B

marc_s

清理好示例XML并正确关闭所有标签后，请尝试以下操作：

DECLARE @input XML = '<Root>
<id>a2bh5</id>
<Student ID="123">
    <Name>abc</Name>
    <course>ETL</course>
    <Scores>
        <Score>
            <Subject>SSIS</Subject>
            <grade>B</grade>
        </Score>
        <Score>
            <Subject>Informatica</Subject>
            <grade>C</grade>
        </Score>
    </Scores>
</Student>
<Student ID="456">
    <Name>xyz</Name>
    <course>ETL</course>
    <Scores>
        <Score>
            <Subject>Pentaho</Subject>
            <grade>F</grade>
        </Score>
        <Score>
            <Subject>Datastage</Subject>
            <grade>A</grade>
        </Score>
    </Scores>
</Student>
</Root>'

SELECT
    StudentID = XStudents.value('@ID', 'int'),
    Course = XStudents.value('(course)[1]', 'varchar(50)'),
    Subject = XScore.value('(Subject)[1]', 'varchar(50)'),
    Grade = XScore.value('(grade)[1]', 'varchar(10)')
FROM
    @Input.nodes('/Root/Student') AS XT1(XStudents)
CROSS APPLY
    XStudents.nodes('Scores/Score') AS XT2(XScore)