In my previous post, “XML Result sets with SQL Server
”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server.
To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in previous post
.Read XML Elements with T-SQL:
DECLARE @SQLYoga TABLE(
ID INT IDENTITY,
CreatedDate DATETIME DEFAULT(GETDATE()),
INSERT INTO @SQLYoga(Data)
SELECT 'Tejas Shah'
SELECT 'Generate XML'
DECLARE @xml XML
SELECT @xml = (
FOR XML PATH('Record'), ROOT('Records')
Now, please find query to read the query to read XML generated above:
x.v.value('ID', 'INT') AS ID,
x.v.value('Data', 'VARCHAR(50)') As Data,
x.v.value('CreatedDate', 'DATETIME') AS CreatedDate
FROM @xml.nodes('/Records/Record') x(v)
This query generates the output as follows:
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.