Category: XML

Querying XML data in SQL – XML Explicit

Below code shows 1.retrieving the data using XML explicit and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts the data into XML column.
declare @a table(sno int,sname xml)
Insert into @a
Select 1,(Select 1 as Tag,Null as Parent,FirstName as [Details!1!FirstName],LastName as [Details!1!LastName],PhoneNumber as [Details!1!PhoneNumber]
From Person.Person Left Outer Join Person.PersonPhone on Person.BusinessEntityID=PersonPhone.BusinessEntityID
Where Person.BusinessEntityID<6
For XML Explicit)
--Verify the data
Select * from @a
--query to retrieve the data in the xml column
Select Name.value('@FirstName','varchar(20)') as [FirstName],
Name.value('@LastName','varchar(20)') as [LastName],
Name.value('@PhoneNumber','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/Details') as Person(Name)

Output : ForXmlExplicit

Advertisement

Querying XML data in SQL – XML Auto

Below code shows 1.retrieving the data using XML auto and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts into the XML column.
Declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML Auto)

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select Sno,Name.value('@FirstName','varchar(20)') as [FirstName],
Name.value('@LastName[1]','varchar(20)') as [LastName],
Name.value('(./Person.PersonPhone/@PhoneNumber)[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/Person.Person') as Person(Name)

Output : ForXmlAuto

Querying XML data in SQL – XML Raw

Below code shows 1.retrieving the data using XML raw and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example,
--Generates XML data and inserts into the XML column.
Declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML RAW('PersonDetails'))

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select Sno,Name.value('(@FirstName)[1]','varchar(20)') as [FirstName],
Name.value('(@LastName)[1]','varchar(20)') as [LastName],
Name.value('(@PhoneNumber)[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/PersonDetails') as Person(Name)

Output : ForXmlRAW

Querying XML data in SQL – XML Path

Below code shows 1.retrieving the data using XML path and 2.querying the XML data using TSQL. I referred AdventureWorks database tables in the example.
--Generates XML data and inserts into the XML column.
declare @a table(sno int,sname xml)
Insert into @a(sno,sname)
Select 1,(Select FirstName,LastName,PhoneNumber
from Person.Person Left Outer Join Person.PersonPhone
on Person.BusinessEntityID=PersonPhone.BusinessEntityID
where Person.BusinessEntityID<6 for XML Path('PersonDetails'))

--Verify the data
Select * from @a

--TSQL Query to get the output from the XML column
Select sno,Person.Name.value('./FirstName[1]','varchar(20)') as [FirstName],
Person.Name.value('./LastName[1]','varchar(20)') as [LastName],
Person.Name.value('./PhoneNumber[1]','varchar(20)') as [PhoneNumber]
from @a cross apply sname.nodes('/PersonDetails') as Person(Name)

Output: ForXmlPath