Querying XML Columns in SQL (CROSS APPLY)

Here is an example on how to query data from XML column 


Hope this is Self Explanatory



create table TestTable (ID int, Name varchar(100), Configuration xml)


insert into TestTable values (1001, 
'Test 1', 
'<Root ID="RootID1"><Child ID="childID1">child name 1</Child><Child ID="childID2">child name 2</Child></Root>')


select t.ID, 
t.Name, 
CChild.child.value('./@ID' , 'varchar(100)') ChildID, 
CChild.child.value('.' , 'varchar(100)') ChildName,
CRoot.root.value('./@ID' , 'varchar(100)') RootID
from TestTable t 
cross apply t.Configuration.nodes('Root/Child' ) as CChild(Child)
cross apply t.Configuration.nodes('Root') as CRoot(root)



select t.ID, 
t.Name, 
CRoot.root.value('./@ID' , 'varchar(100)') RootID, 
CRoot.root.value('./Child[1]' , 'varchar(100)') ChildName,
CRoot.root.value('./Child[1]/@ID' , 'varchar(100)') ChildID
from TestTable t 
cross apply t.Configuration.nodes('Root') as CRoot(root)

awesome comments