Getting XML node names and values in tabular format


declare @xml xml

set @xml = '<Employee>

<id>1</id>

<name>xxx</name>

<marks>45</marks>

<class>7</class>

</Employee>'

 

select x.value('local-name(.)','varchar(50)') as attributename,

x.value('.','varchar(50)') as value

from @xml.nodes('/*/*') n(x)

 

The output will look like this:

Attributename Value
id 1
Name xxx
Marks 45
Class 7