Read child node along with its parent attribute from XML in SQL server

Question

I have an XML similar to this structure.

<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>

I would like to get details from child nodes(scores) along with its parent attribute(Id) using Xquery in SQL server.

The query result is expected like below for all subjects. Please help.

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

Answer 1

Once your sample XML is cleaned up and all tags are properly closed, try this:

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)

This gives an output of:

Answer 2

First, you need to fix your XML. Your Name, course, Subject, and grade elements don't have proper closing tags. Also, element names are case-sensitive in XML; Subject and subject are not the same thing.

Once you've done that, you can use the nodes() method to break the XML into rows and then extract the data you want. Like so:

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)

Results:

Student ID   Subject       Grade
---------------------------------
123          SSIS          B
123          Informatica   C
456          Pentaho       F
456          Datastage     A