Donate

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
Show SQL Query Result As XML Using Sql Server FOR XML()
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');

Comments

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

How To Insert Or Add Emojis In Microsoft Teams Status Message

Bootstrap Modal In ASP.NET MVC With CRUD Operations