Home » , » convert xml to table in sql server 2005

convert xml to table in sql server 2005

Penulis : Sandeep Mittal on Monday, January 9, 2012 | 1/09/2012 04:14:00 PM

In this post, I am sharing how to read nodes of XML and display it in Tabular Form. 

XML Declaration
Above declared XML is a collection of Employee details.

Now Let's write a query to fetch the nodes from the XML Variable and display the employee details in Tabular Form

Query to get data in Table from XML Variable
SELECT EmpID = Node.Data.value('(EmpID)[1]', 'VARCHAR(MAX)')
        , [First Name] = Node.Data.value('(FirstName)[1]', 'VARCHAR(MAX)') 
        , [Last Name] = Node.Data.value('(LastName)[1]', 'VARCHAR(MAX)') 
        , DOB = Node.Data.value('(DOB)[1]', 'VARCHAR(MAX)')
FROM    @XML.nodes('/NewDataSet/Employee') Node(Data)
Share this article :

+ comments + 1 comments

September 15, 2012 at 10:35 AM

Awesome..Nice one!!!

Post a Comment