There are two ways to have an XML from query:
1. FOR XML RAW
2. FOR XML PATH
Please find following sample queries where both of the way are being used:
Lets create sample data
DECLARE @SQLYoga TABLE(
ID INT IDENTITY,
Data VARCHAR(50),
CreatedDate DATETIME DEFAULT(GETDATE()),
CreatedBy INT
)
INSERT INTO @SQLYoga(Data)
SELECT 'SQLYoga'
UNION ALL
SELECT 'Tejas Shah'
UNION ALL
SELECT 'Generate XML'
Generate XML with XML ROW
SELECT *
FROM @SQLYoga
FOR XML RAW, ROOT('Records')
XML RAW, returns the XML by each record with row element and all columns as attributes in the XML.
Generate XML with XML PATH
SELECT *
FROM @SQLYoga
FOR XML PATH('Record'), ROOT('Records')
XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record.
That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this.
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015