Querying XML data in SQL – XML Auto

XML 1 Minute

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

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published