Чтение дочернего узла вместе с его родительским атрибутом из XML на сервере SQL

У меня есть 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>

Я хотел бы получить подробную информацию о дочерних узлах (оценках) вместе с его родительским атрибутом (Id), используя Xquery на сервере SQL.

Результат запроса ожидается, как показано ниже, для всех предметов. Пожалуйста помоги.

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

sql-server xquery-sql
person Jasqlg    schedule 16.06.2015    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Как только ваш образец 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)

Это дает результат:

введите здесь описание изображения

person marc_s    schedule 16.06.2015

arrow_upward
2
arrow_downward

Во-первых, вам нужно исправить ваш XML. Ваши элементы Name, course, Subject и grade не имеют соответствующих закрывающих тегов. Кроме того, имена элементов в XML чувствительны к регистру; Subject и subject не одно и то же.

Сделав это, вы можете использовать nodes(), чтобы разбить XML на строки, а затем извлечь нужные данные. Вот так:

declare @test 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
    [Student ID] = N.x.value('(../../@ID)[1]', 'bigint'),
    [Subject] = N.x.value('(./Subject)[1]', 'varchar(64)'),
    [Grade] = N.x.value('(./grade)[1]', 'char(1)')
from
    @test.nodes('/Root/Student/Scores/Score') N(x)

Полученные результаты:

Student ID   Subject       Grade
---------------------------------
123          SSIS          B
123          Informatica   C
456          Pentaho       F
456          Datastage     A
person Joe Farrell    schedule 16.06.2015