XML NODE

Question

I have a table (Table1) which has a coloumn by name XMLColoumn a part of the xml is like

<StudentSubjects>
   <ValueName>Maths</ValueName>
   <ValueName>Science</ValueName>
   <ValueName>History</ValueName>
   <ValueName>Calculus</ValueName>
</StudentSubjects>

My table is (Table1)

StudentNo   XMLColoumn(textfile)
 112           (above XML)
 1445          (same structure as above XML)

I am trying to get output as

StudentNo     Subjects
112           Maths
112           Science
112           History
112           Calculus

I am trying to do something like

SELECT
     convert(XML,CAST(XMLCOLOUMN AS nvarchar(max)).value('(//StudentSubjects/ValueName/text())[1]','nvarchar(max)'),StudentNo from Table1

Which returns me only the first row ie maths..how can i get all the

<ValueName>?

Please let me know i tried a lot to find...but couldnt.Please help!

Answer 1

You need to use CROSS APPLY to expand the nodes of the XML column. i.e.

DECLARE @Student TABLE
    (
     StudentNo INT IDENTITY
    ,StudentSubjects XML
    )

INSERT  @Student
        (
         [StudentSubjects]
        )
        SELECT 
        '<StudentSubjects>
       <ValueName>Maths</ValueName>
       <ValueName>Science</ValueName>
       <ValueName>History</ValueName>
       <ValueName>Calculus</ValueName>
       </StudentSubjects>'
        UNION ALL
        SELECT  
        '<StudentSubjects>
        <ValueName>Maths</ValueName>
        <ValueName>Science</ValueName>
        <ValueName>History</ValueName>
        <ValueName>Calculus</ValueName>
       </StudentSubjects>'


SELECT  [StudentNo]
,       x.i.value('text()[1]', 'nvarchar(max)')
FROM    @Student AS S
        CROSS APPLY StudentSubjects.nodes('/StudentSubjects/ValueName') x (i)

Moreover, I do not know your environment but having student subjects in xml format seems to be a very bad option as it makes a bad schema design.

XML NODE

Follow this question

Related Questions