Rahul's space

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 )