How to Retrieve data in XML Format in SQL SERVER?
Suppose you have a requirement to get the data from a SQL Server table in XML format.
Lets say you have a table named DimPromotion as shown below, and you are asked to retrieve data in XML Format from table. How will you do that in SQL Server ?
SELECT PromotionKey, PromotionAlternateKey, EnglishPromotionName, SpanishPromotionName FROM DimPromotion
The answer is pretty simple, to retrieving the data in XML format from a table you can use FOR XML <option> Clause.
Lets look at on various options that can be used with FOR XML clause, to retrieving the data from a table in XML format.
Using 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 you click on link in grid result, it opens a XML data in separate query editor window.
As you can see, the XML node is displayed as a row that contains each row data of a table.
Using FOR XML AUTO
FOR XML AUTO clause is similar to FOR XML RAW, but the only difference is that 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
As you can see, a node name is considered as table name.
Using 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 you click on link in grid result, it opens the XML data in separate query editor window as shown below.
As you can see XML string contains a root and element tag.
Using 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 named ‘Promotion’ and element tag named ‘PromotionMaster’.
SELECT PromotionKey, PromotionAlternateKey, EnglishPromotionName,
SpanishPromotionName from DimPromotion
FOR XML PATH('PromotionMaster') , ROOT('Promotion')
When you click link in grid result, it opens the XML data in separate query editor window as shown below.
and you can see, the root tag and element tag there.
Also Read..
