none
Parsing XML and getting results as a table

    Question

  • Hi all,

    For the following XML variable:

    DECLARE @XML XML
    SET @XML =
    '<Input>
        <Courses>
            <Course>
                <Id>27</Id>
                <Students>
                    <Id>19876</Id>
                    <Id>19878</Id>
                </Students>
            </Course>
            <Course>
                <Id>29</Id>
                <Students>
                    <Id>19879</Id>
                </Students>
            </Course>
        </Courses>   
    </Input>'

    I need to query courses and students in each course as a list.

    For this I have done as following:

    DECLARE @StudentsXML XML
    SELECT @StudentsXML = @XML.query('/Input/Courses')

    SELECT TBL.ITEM.value('.', 'INT') AS CourseId, TABL.ITEM.value('.', 'INT') AS Students
    FROM    @StudentsXML.nodes('//Courses/Course/Id') AS TBL(ITEM) OUTER APPLY
                 @StudentsXML.nodes('//Courses/Course//Students/Id') AS TABL(ITEM)

    I am getting the result as:

    CourseId

    Students

    27

    19876

    27

    19878

    27

    19879

    29

    19876

    29

    19878

    29

    19879

    But this is wrong, because the course 27 has only 2 students and 29 has only one student. But all the three students are shown for both the courses.

    Please suggest if there is any method available for parsing the XML and retrieving the data correctly as per the relations present in XML

    Thanks in advance,

    Surya.


    Surya Praveen
    Wednesday, September 21, 2011 4:43 AM

Answers

  • Take a look at this (took me a while to get there):

    DECLARE @XML XML 
    SET @XML =
    '<Input>
        <Courses>
            <Course>
                <Id>27</Id>
                <Students>
                    <Id>19876</Id>
                    <Id>19878</Id>
                </Students>
            </Course>
            <Course>
                <Id>29</Id>
                <Students>
                    <Id>19879</Id>
                </Students>
            </Course>
        </Courses>    
    </Input>'
    
    select
    	T.n.value('(Id/text())[1]','int') as [CourseID],
    	P.x.value('(text())[1]','int') as [Student ID]
    FROM
    	@XML.nodes('/Input/Courses/Course') AS T(n)
    	OUTER APPLY  T.n.nodes('Students/Id') AS P(x)
    	;
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by HunchbackMVP Wednesday, September 21, 2011 6:44 PM
    • Marked as answer by KJian_ Tuesday, September 27, 2011 8:55 AM
    Wednesday, September 21, 2011 5:27 AM
    Moderator

All replies

  • Check this:

    DECLARE @XML XML

    SET @XML =
    '<Input>
        <Courses>
            <Course>
                <Id>27</Id>
                <Students>
                    <Id>19876</Id>
                    <Id>19878</Id>
                </Students>
            </Course>
            <Course>
                <Id>29</Id>
                <Students>
                    <Id>19879</Id>
                </Students>
            </Course>
        </Courses>  
    </Input>'
    
    select t.c.value('../../Id[1]', 'INT') as CourseId,
        t.c.value('.', 'INT') as Students
    from @XML.nodes('//Input/Courses/Course/Students/Id') as t(c)
    
    



    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, September 21, 2011 5:24 AM
  • Take a look at this (took me a while to get there):

    DECLARE @XML XML 
    SET @XML =
    '<Input>
        <Courses>
            <Course>
                <Id>27</Id>
                <Students>
                    <Id>19876</Id>
                    <Id>19878</Id>
                </Students>
            </Course>
            <Course>
                <Id>29</Id>
                <Students>
                    <Id>19879</Id>
                </Students>
            </Course>
        </Courses>    
    </Input>'
    
    select
    	T.n.value('(Id/text())[1]','int') as [CourseID],
    	P.x.value('(text())[1]','int') as [Student ID]
    FROM
    	@XML.nodes('/Input/Courses/Course') AS T(n)
    	OUTER APPLY  T.n.nodes('Students/Id') AS P(x)
    	;
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by HunchbackMVP Wednesday, September 21, 2011 6:44 PM
    • Marked as answer by KJian_ Tuesday, September 27, 2011 8:55 AM
    Wednesday, September 21, 2011 5:27 AM
    Moderator

  • select t.c.value('Id[1]', 'INT') as CourseId,
        t1.c1.value('.', 'INT') as Students
    from @XML.nodes('//Input/Courses/Course') as t(c)
    cross apply t.c.nodes('Students/Id') as t1(c1)
    

    ... this one is more optimized code.
    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Wednesday, September 21, 2011 5:29 AM
  • Hi Surya Praveen,

    there is another approach to implement this. please try the following code.

    DECLARE @XML XML,@temp xml
    SET @XML =
    '<Input>
        <Courses>
            <Course>
                <Id>27</Id>
                <Students>
                    <Id>19876</Id>
                    <Id>19878</Id>
                </Students>
            </Course>
            <Course>
                <Id>29</Id>
                <Students>
                    <Id>19879</Id>
                </Students>
            </Course>
        </Courses>    
    </Input>'
    
    set  @temp = @XML.query('for $a in /Input/Courses/Course/Id,$b in /Input/Courses/Course/Students/Id where $a/.. is $b/../..  return element detail {attribute CourseID {string($a)},attribute StudentID {string($b)}}')
    select t.c.value('@CourseID','int') as [CourseID],t.c.value('@StudentID','int') as [StudentID] from @temp.nodes('/detail') as t(c)
    

    Wednesday, September 21, 2011 9:06 AM