Monday, January 9, 2012

convert xml to table in sql server 2005

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

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

1 comments:

Post a Comment