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 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..

FOR JSON Clause

JSON_QUERY

JSON_MODIFY

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 1,214 total views,  2 views today

Leave a Reply

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