Advertisements
Home > Sql Server > Retrive data from xml in Tabular Format from SQL Server

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 )

 

 

Advertisements
Categories: Sql Server
  1. No comments yet.
  1. February 29, 2012 at 10:00 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: