Jan 9, 2012

Convert XML to Table in SQL Server

In this post, I am sharing the script to read nodes of XML and display it in Tabular Form in SQL Server

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)

    Choose :
  • OR
  • To comment
1 comment:
Write comments