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
DECLARE @XML XML
SET @XML =
'<NewDataSet>
    <Employee>
       <EmpID>EMP001</EmpID>
       <FirstName>Sandeep</FirstName>
       <LastName>Mittal</LastName>
       <DOB>08/25/1981</DOB>
    </Employee>
    <Employee>
       <EmpID>EMP002</EmpID>
       <FirstName>Abhay</FirstName>
       <LastName>Kumar</LastName>
       <DOB>12/01/1982</DOB>
    </Employee>
</NewDataSet>'

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)
Output

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Previous
Next Post »

1 comments:

comments