Querying XML data in SQL – XML Explicit

XML 1 Minute

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

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