Thursday, July 19, 2012

Getting XML Output from SQL Query - Part 2

In Part 1 of this series we discusses the syntax that drives the query. In this post let us dig a bit deeper and actually write some queries that work. We will start off with a simple one and let the intricacies unfold.
I am assuming that you have  database table say "Students" as below,
studentId studentName age class parentId
1 Ankur 8 4 3
2 Akshay 7 2 1
3 Deepak 8 3 3
4 Esha 6 1 4
5 Pankaj 7 2 2
6 Raj 9 4 1
7 Rahul 8 3 5
8 Sahana 7 2 5
9 Samir 9 4 2
10 Triveni 8 3 3

and a parents table as below

parentId fathersName mothersName fathersOccupation mothersOccupation
1 Father1 Mother1 Occupation11 Occupation21
2 Father2 Mother2 Occupation12 Occupation22
3 Father3 Mother3 Occupation13 Occupation23
4 Father4 Mother4 Occupation14 Occupation24
5 Father5 Mother5 Occupation15 Occupation25

Let the fun start. What we will do first is just create an XML that lists all the students in XML format and then start into churning a more meaningful XML.
Select * From students Where studentId = 1
For XML Auto

<students studentId="1" studentName="Ankur" age="8" class="3" parentId="4" />

Select * From students Where studentId = 1
FOR XML RAW('Student');

<Student studentId="1" studentName="Ankur" age="8" class="3" parentId="4" />

Select * From students Where studentId = 1
FOR XML RAW('Student'), Root('Students');

<Students>
   < Student studentId="1" studentName="Ankur" age="8" class="3" parentId="4" />
</Students>

Select * From students Where studentId = 1
FOR XML PATH('Student'), Root('Students');

<Students>
   <Student>
     <studentId>1</studentId>
     <studentName>Ankur</studentName>
     <age>8 </age>
     <class>3 </class>
     <parentId>4</parentId>
   </Student>
</Students>

No comments :