Blog Detail

SQL Yoga – Read XML with T-SQL statement in SQL SERVER
03 May 2013
Tejas Shah
/Project/ImageURL/SQLYoga Resultset of XML PATH_thumb[8].png
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,
    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'
DECLARE @xml XML
SELECT @xml = (
        SELECT    * 
        FROM    @SQLYoga
        FOR XML PATH('Record'), ROOT('Records')
    )
SELECT @xml
SQLYoga Resultset of XML PATH
 Now, please find query to read the query to read XML generated above:
SELECT
        x.v.value('ID[1]', 'INT') AS ID,
        x.v.value('Data[1]', 'VARCHAR(50)') As Data,
        x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate
FROM    @xml.nodes('/Records/Record') x(v)
This query generates the output as follows:
SQLYoga Read XML with T-SQL

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.

Tags

SQLYoga Tejas Shah SQL Server XML T-SQL

About me

User

Tejas Shah

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.

Read More

Categories

Recent Post