Example1 on how to read xml in sql server
declare
@xmlfullstring xml
set
@xmlfullstring= '<root>
<name>madhan</name>
<name>kumar</name>
</root>'
select xfs.name.value('name[1]','varchar(50)') as namexml,
xfs.name.value('name[2]','varchar(50)') as namexml
from
@XmlFullString.nodes('root')xfs(name)
Example2 on how to read xml in sql server
DECLARE @Details xml
SET
@Details = '<root>
<SelectedCategory
CategoryId="101">
<ItemID>120</ItemID>
<ItemID>256</ItemID>
</SelectedCategory>
<SelectedCategory
CategoryId="125">
<ItemID>158</ItemID>
<ItemID>120</ItemID>
</SelectedCategory>
<SelectedCategory
CategoryId="15">
<ItemID>5986</ItemID>
<ItemID>20</ItemID>
<ItemID>268</ItemID>
</SelectedCategory>
</root>'
SELECT T.[CategoryID],
T2.Loc.query('.').value('.', 'INT') Val
FROM
( SELECT SelCat.CatDet.value('(@CategoryId)[1]', 'int') as CategoryID,
SelCat.CatDet.query('.') as ItemID
FROM
@Details.nodes('/root/SelectedCategory')
as SelCat(CatDet)
)
T CROSS APPLY ItemID.nodes('/SelectedCategory/ItemID') as T2(Loc)
Example3 on how to read xml in sql server
declare
@studentxml xml ='
<Students>
<Student>
<Id>1</Id>
<Name>Madhan</Name>
</Student>
<Student>
<Id>2</Id>
<Name>Karthi</Name>
</Student>
<Student>
<Id>3</Id>
<Name>Bata</Name>
</Student>
</Students>'
SELECT X.Student.query('Id').value('.', 'INT') as studentid,
X.Student.query('Name').value('.', 'VARCHAR(50)') as studentname
FROM
@studentxml.nodes('/') as a(b)
CROSS APPLY b.nodes('Students/Student') AS X(Student);
