May 6, 2013

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

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.

Reference: Tejas Shah (www.SQLYoga.com)

No comments:

Post a Comment