September 5, 2012 at 11:40 am
Hello,
I'm having a XML file in this structure
<Class>
<ClassNumber>111</ClassNumber>
<Student>
<Student_ID>1</Student_ID>
<Student_ID>2</Student_ID>
<Student_ID>3</Student_ID>
</Student>
</ClassNumber>
</Class>
<Class>
<ClassNumber>222</ClassNumber>
<Student>
<Student_ID>4</Student_ID>
<Student_ID>5</Student_ID>
<Student_ID>6</Student_ID>
</Student>
</ClassNumber>
</Class>
I want the out put like
Class # Stud Id
111 1
111 2
111 3
222 4
222 5
222 6
Please help. I'm trying for a very long time.
Regards,
mistry
September 5, 2012 at 12:42 pm
you're very likely having an issue because your XML structure isn't valid. For one thing you're closing tags that have already been closed (which SQLXML will complain about).
That said - if you clean up your XML, you could do something like the following:
declare @x xml
set @x = '<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>'
select c.value('ClassNumber[1]','int'),
s.value('.[1]','int')
from @x.nodes('/Class') x(c)
cross apply c.nodes('Student/Student_ID') st(s)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply