Home » How to Retrieve data in XML Format in SQL SERVER

How to Retrieve data in XML Format in SQL SERVER

How to Retrieve data in XML Format in SQL SERVER ?





Suppose you have a requirement to get the data from a SQL Server table – DimPromotion (as given below) in XML format . How will you do that in SQL Server ?

 

 

So to retrieve data in XML format from a table we can use FOR XML <option> Clause.

Lets look at on various options that can be used with FOR XML clause , to retrieve the data from a table in XML format.

FOR XML RAW

Following Statement uses FOR XML RAW clause that returns the data from a table in Raw XML format and the XML node name will be row that represent each row of a table.

SELECT PromotionKey, PromotionAlternateKey, EnglishPromotionName, SpanishPromotionName

FROM DimPromotion

FOR XML RAW

If we click on Grid Result set , It opens a XML data in separate editor window . As we can see , here XML node is displayed as a row that contains each row data of a table.

 

FOR XML AUTO

FOR XML AUTO clause is similar to FOR XML RAW, but the only difference is that here XML node will be the database table name .

Following Statement uses FOR XML AUTO clause that returns the data from a table in raw format and the XML node name will be a table name.

SELECT PromotionKey, PromotionAlternateKey, EnglishPromotionName, SpanishPromotionName

FROM DimPromotion

FOR XML AUTO





FOR XML PATH

It adds the element tag to XML result set . Following Statement uses FOR XML PATH clause that returns the data from a table in XML format adding element ‘PromotionMaster’ to XML string.

select PromotionKey, PromotionAlternateKey, EnglishPromotionName, SpanishPromotionName from DimPromotion
FOR XML PATH(‘PromotionMaster‘)

 

If we click on grid result set , it opens the XML data in separate editor window as given below.

As we can see XML string having a root and element tag.

FOR XML ROOT

It adds the root tag to XML string , Following Statement uses FOR XML ROOT clause that returns the data from a table in XML format that will have root tag – ‘Promotion’ and element tag – ‘PromotionMaster’.

select PromotionKey, PromotionAlternateKey, EnglishPromotionName, SpanishPromotionName from DimPromotion
FOR XML PATH(‘PromotionMaster‘) , ROOT(‘Promotion‘)

When we click on Grid result set ,it opens the XML data in separate editor window as given below.

 

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.