x

XML NODE

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!
more ▼

asked Apr 25, 2012 at 10:41 PM in Default

palum gravatar image

palum
249 25 29 30

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.
more ▼

answered Apr 26, 2012 at 04:26 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

@palum And just in case if you have some records in your table which don't have value in the column storing the subjects in xml format but still need to include these records in your results then you can use the outer apply in place of cross apply, i.e.

SELECT
[StudentNo], x.i.value('text()[1]', 'nvarchar(max)') Subjects FROM @Student AS S outer apply StudentSubjects.nodes('/StudentSubjects/ValueName') x(i)
Apr 26, 2012 at 05:55 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x279

asked: Apr 25, 2012 at 10:41 PM

Seen: 1113 times

Last Updated: Apr 26, 2012 at 05:55 PM