views:

95

answers:

1

Hi, I have a table having 2 columns EmployeeId (int) and EmployeeDetails(XMl type)

EmployeeId EmployeeDetails

1 <Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee>

2 <Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age> 28 </Age><Address> Australia</Address></EmployeeDetails></Employee>

I need to have the output like

EmployeeId EmpName Age Address


1 Priyanka 24 Argentina

2 Sarkar 28 Australia

Please help as I am new to xml

+4  A: 

Books Online is a bit indecipherable on the subject of XML; you may want to try this site for help: http://blogs.msdn.com/mrorke/archive/2005/06/01/423965.aspx Here's a snippet of code to help you along.

DECLARE @Employees TABLE
    (
      EmployeeID INT ,
      EmployeeDetails XML
    )

INSERT  INTO @Employees
        ( EmployeeID ,
          EmployeeDetails 
        )
VALUES  ( 1 ,
          '<Employee><EmployeeDetails><EmployeeName> Priyanka </EmployeeName><Age> 24 </Age><Address> Argentina</Address></EmployeeDetails></Employee>'

        )


INSERT  INTO @Employees
        ( EmployeeID ,
          EmployeeDetails 
        )
VALUES  ( 2 ,
          '<Employee><EmployeeDetails><EmployeeName> Sarkar </EmployeeName><Age> 28 </Age><Address> Australia</Address></EmployeeDetails></Employee>'
        )


SELECT  EmployeeID ,
        EmployeeName = m.value('EmployeeName[1]', 'varchar(50)') ,
        Age = m.value('Age[1]', 'int') ,
        Address = m.value('Address[1]', 'varchar(100)')
FROM    @Employees e
        CROSS APPLY EmployeeDetails.nodes('/Employee/EmployeeDetails') AS e1 ( m )
Stuart Ainsworth
Thank you very much sir. Also 1 more thing. Sir as I told that using xml is new for me. Can you pls explain the code a bit
priyanka.sarkar
I'll try; I'm a bit new myself. The CROSS APPLY is used to join the non-xml elements with the xml nodes in order to have a mixed result set. The xml.nodes method is used to shred the xml into rows, using the specified path as the reference point. e1(m) gives you an alias to use in the query, and the xml.value method looks at each aliased node for the tag of interest, and casts it to the appropriate SQL Server type. You'll have to play with it to really start understanding it, but I hope that helps.
Stuart Ainsworth