Retrive data from xml in Tabular Format from SQL Server
If you have a table structure like following :
stdData contain following data:
‘<student>
<row>
<subject>Physics</subject>
</row>
<row>
<subject>Biology</subject>
</row>
</student>’
For retrieving all subject value from each row of a xml use following query
Query :
//Find all subject value from each row of a xml
SELECT y.query(‘.’).value(‘(/row/subject)[1]’,’nvarchar(50)’) AS ‘Subject’ FROM tblStudent
CROSS APPLY stdData.nodes(‘student/row’) x(y)
output :
Subject
———
Physics
Biology
———————————————————————————–
Example 2 :
DECLARE @x XML
SET @x =
‘<Employees>
<Employee ID=”101″>
<Name>Jacob</Name>
<Department>IT</Department>
</Employee>
<Employee ID=”354″>
<Name>Steve</Name>
<Department>IT</Department>
</Employee>
<Employee ID=”456″>
<Name>Bob</Name>
<Department>IT</Department>
</Employee>
<Employee ID=”478″>
<Name>Joe</Name>
<Department>IT</Department>
</Employee>
<Employee ID=”981″>
<Name>Louis</Name>
<Department>IT</Department>
</Employee>
</Employees>’
SELECT y.value( ‘Name[1]‘, ‘VARCHAR(50)’ ) AS Name,
y.value( ‘Department[1]‘, ‘VARCHAR(50)’ ) AS department
FROM @x.nodes(‘Employees/Employee’) x ( y )
-
February 29, 2012 at 10:00 pmImplementing Sql Dynamic Pivot Query « Harsh Baid
Comments