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

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

Regards, Frank

Have a look at the xml data type methods. You should use nodes and value. — Mikael Eriksson
– Mikael Eriksson, Commented Sep 5, 2012 at 17:49

C. M. Sperberg-McQueen · Accepted Answer · 2012-09-05 17:55:35Z

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:

answered Sep 5, 2012 at 17:55

C. M. Sperberg-McQueen

25.2k6 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1649662 Over a year ago

Hi, Thanks for your quick reply. Can you give this in SQL server fromat? I'm confused. Sorry abt it...

C. M. Sperberg-McQueen Over a year ago

Sorry; I know that SQL server supports XQuery, so something in the general shape I gave should work, but I do not have access to a running copy of SQL server so I do not know how the example needs to be modified for use in that product.

Collectives™ on Stack Overflow

SQL Server XML load

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related