Example: Using .nodes() to shred XML
I like doing things the simple way I guess. Here's an example of using the .nodes() method of an XML-type instance to produce a tabular shred of its data. Not hard once you figure out that its giving a new current context for each row.
-- Change to a suitable value for your system
use scratch
go
-- cleanup, if needed.
drop table template
go
-- What we want our shred to look like
-- but not where we want the data stored
create table template
(
serialized xml
, nodeText nvarchar(max)
)
go
-- Cleanup, if needed.
drop table #temp
go
-- Get some XML to shred
declare @x xml
set @x='<a><b>100</b><b>200</b><b>400</b><b>300</b></a>'
-- Shred it.
-- note the in the top .query, we get each 'b' node
-- one at time, so '.' is the proper query for that node's xml
-- the .value call is done to show how to fetch the innerText.
select col.query('.') serialized
,col.value('.[1]','nvarchar(max)') nodeText
into #temp
-- The nodes command will return a current context list
-- for each node resulting from its query, whereas
-- the 'hanNode' provides a table shape to apply.
from @x.nodes('/a/b') template(col)
go
-- Behold the joy that is .nodes!
select * from #temp
go
Now if I could just select * from #temp into template...