SQL Server XML load

Question

I have a XML file with this structure

<Class>
    <ClassNumber>111</Flight_Number>
        <Student>
            <Student_ID>1</Student_ID>
            <Student_ID>2</Student_ID>
            <Student_ID>3</Student_ID>
        </Student>
    </ClassNumber>
</Class>
<Class>
    <ClassNumber>222</Flight_Number>
        <Student>
            <Student_ID>4</Student_ID>
            <Student_ID>5</Student_ID>
            <Student_ID>6</Student_ID>
        </Student>
    </ClassNumber>
</Class>

I want the output to be like

111 1
111 2
111 3
222 4
222 5
222 6

Please help. I'm trying for a very long time.

Regards, Frank

Answer 1

Your first problem is that the input you show is not XML: </Flight_Number> does not close any open element, and you do not have a root element for the document.

If the intended input is like the $doc variable in the query below, then the XQuery expression you want will have the structure given here:

let $doc := <Classes>
              <Class>
                <ClassNumber>111</ClassNumber>
                <Student>
                  <Student_ID>1</Student_ID>
                  <Student_ID>2</Student_ID>
                  <Student_ID>3</Student_ID>
                </Student>
              </Class>
              <Class>
                <ClassNumber>222</ClassNumber>
                <Student>
                  <Student_ID>4</Student_ID>
                  <Student_ID>5</Student_ID>
                  <Student_ID>6</Student_ID>
                </Student>
              </Class>
            </Classes>
for $s in $doc//Student_ID
return concat($s/../../ClassNumber, ' ', string($s), '&#xA;')

In the XQuery engine I used to test this, this produced the desired results:

111 1
 111 2
 111 3
 222 4
 222 5
 222 6