Tuesday, September 6, 2016

Show SQL query result as XML using Sql Server FOR XML()

Hello,
Given that you are required to show the results of a query into an XML format, T-SQL has a function called For XML to achieve that output as shown below.
Screenshot
I have created two scripts to achieve the desired result as above. The first script will alias a column name with @AttributeName that signifies an attribute which will then be added to Assigned node using FOR XML Path() function.
Use NorthwindCrudApp
Go
SELECT TOP 3
         ProductID as "@ProductID", 
  ProductName as "@ProductName",
  UnitsInStock as "@UnitsInStock",
  UPPER(ProductName) as [text()] 
FROM Products As Products
 ORDER BY ProductID
FOR XML PATH('Assigned'), Root('DATA');
The second script also creates column alias for each column using the syntax NodeName/@Attribute similar to an XPath expression.
SELECT TOP 3
     ProductID as "Assigned/@ProductID",  
  ProductName as "Assigned/@ProductName",
  UnitsInStock as "Assigned/@UnitsInStock",
  Upper([ProductName]) as "Assigned" 
FROM Products As Products
 ORDER BY ProductID
FOR XML PATH(''),TYPE,ELEMENTS,ROOT('DATA');

0 comments:

Post a Comment