Read XML Tags using SQL Query

Question

<University>
  <RegistrationCode>12859</RegistrationCode>
  <Colleges>
    <College>
      <CollegeCode>0001</CollegeCode>
      <CollegeName>NIMS</CollegeName>
    </College>
    <College>
      <CollegeCode>0002</CollegeCode>
      <CollegeName>OXFORD</CollegeName>
    </College>
  </Colleges>
</University>

I want extract below out put from above XML using SQL Query. Can someone help me on this?

RegistrationCode   CollegeCode      CollegeName
-----------------------------------------------
12859              0001             NIMS
12859              0002             OXFORD

Possible duplicate: stackoverflow.com/questions/15680259/parse-xml-in-sql-server — crush
– crush, Commented May 31, 2013 at 14:21

marc_s · Accepted Answer · 2013-05-31 15:27:54Z

Try this:

DECLARE @input XML = '<University>
<RegistrationCode>12859</RegistrationCode>
<Colleges>
<College>
<CollegeCode>0001</CollegeCode>
<CollegeName>NIMS</CollegeName>
</College>
<College>
<CollegeCode>0002</CollegeCode>
<CollegeName>OXFORD</CollegeName>
</College>
</Colleges>
</University>'

SELECT
    RegistrationCode = Univ.value('(RegistrationCode)[1]', 'int'),
    CollegeCode = Colleges.value('(CollegeCode)[1]', 'int'),
    CollegeName = Colleges.value('(CollegeName)[1]', 'varchar(100)')
FROM 
    @input.nodes('University') AS XTbl(Univ)
CROSS APPLY
    Univ.nodes('Colleges/College') AS XTbl2(Colleges)

That gives me your desired output.

Basically, you need to first have a list of <University> nodes (just one here) to get the registration code, and then from that <University> node you grab the <Colleges>/<College> subnodes using CROSS APPLY and you grab the college code and name from those subnodes

Collectives™ on Stack Overflow

Read XML Tags using SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related