.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
abhays
Clintonzz
cathyhill345
arronlee
mattyclown

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Get XML String from SQL Server as an OUTPUT

Posted By:Pankaj Mishra       Posted Date: December 07, 2008    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

Get XML String from SQL Server as an OUTPUT.

This article explains to get the XML String of  records from the SQL Server using Select Statement. SQL Server 2000, 2005 and 2008 has the capability to return the records as XML String using the keywords FOR XML AUTO, FOR XML RAW
and FOR XML EXPLICIT
appending on your SQL Query.

There are three modes of format; you can get the XML string output as your result from your SQL Query.

  1. AUTO MODE
  2. RAW MODE
  3. EXPLICIT MODE


1) Auto Mode: Using For XML Auto keyword, you can get the record as Nested XML element and for each master record; you will get inner XML element and Column names and their value display as the XML Attribute.

For Eg.,

This is our XML Query:

SELECT dbo.Employee_Master.Designation,EmpDet_ID,Employee_Details.Employee_ID,FirstName,MiddileName,LastName

FROM  dbo.Employee_Master

INNER JOIN dbo.Employee_Details

ON  Employee_Details.Employee_ID =Employee_Master.Employee_ID

ORDER BY Employee_Details.Employee_ID

FOR XML AUTO

And the out put of above SQL Server Query is like this:









2) Raw Mode : Using For XML Raw keyword, you can get the record as an XML string format of the SQL Query. In this mode for each record you can get each XML Element.

For Eg.,
This is our XML Query

SELECT dbo.Employee_Master.Designation,EmpDet_ID,Employee_Details.Employee_ID,FirstName,MiddileName,LastName

FROM  dbo.Employee_Master

INNER JOIN dbo.Employee_Details

ON  Employee_Details.Employee_ID =Employee_Master.Employee_ID

ORDER BY Employee_Details.Employee_ID

FOR XML RAW

Here is the Out Put XML string



Explicit Mode : Using For XML Explicit keyword, you can get The XML result in different manners. It depends on how you write your SQL Query. Writing SQL Query with XML Explicit Mode is little more complex and you have to use UNION ALL to use XML Explicit option.

For each level you have to use Tag and Parent. If you are at top level of your SQL Query, then you have to use 1 as Tag and Null as Parent. If you are at second level of query you have to use 2 as Tag and 1 as Parent and so on.

For Eg.

SELECT 1 as Tag, NULL AS Parent,

            MAST.Employee_ID AS [Employee_Master!1!Employee_ID],

            NULL AS [Employee_Details!2!LastName]

FROM  dbo.Employee_Master MAST

 

UNION ALL

 

SELECT 2 as Tag, 1 as Parent,

       MAST.Employee_ID AS [Employee_Master!1!Employee_ID],

         DET.LastName AS [Employee_Details!2!LastName]

      

FROM dbo.Employee_Master MAST INNER JOIN  dbo.Employee_Details DET ON MAST.Employee_ID = DET.Employee_ID

 

FOR XML EXPLICIT

The above one is our SQL Query if you don't want to use Order by clause. The output of the above SQL Query will be:







With ORDER BY Clause  our SQL Query will be like this:

SELECT 1 as Tag, NULL AS Parent,

            MAST.Employee_ID AS [Employee_Master!1!Employee_ID],

            NULL AS [Employee_Details!2!LastName]

FROM  dbo.Employee_Master MAST

 

UNION ALL

 

SELECT 2 as Tag, 1 as Parent,

       MAST.Employee_ID AS [Employee_Master!1!Employee_ID],

         DET.LastName AS [Employee_Details!2!LastName]

      

FROM dbo.Employee_Master MAST INNER JOIN  dbo.Employee_Details DET ON MAST.Employee_ID = DET.Employee_ID

 

ORDER BY [Employee_Master!1!Employee_ID], [Employee_Details!2!LastName]

 

FOR XML EXPLICIT

 And the output will be









Hope this will be helpful to beginners

Cheers
Pankaj


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend