This was from an email I sent to some peeps for a quickie how-to...I was told that this kind of junk was worth posting on a blog. Of course I want to do my part in cluttering up the world...
DECLARE @x as xml
SET @x = '<test att="1">
<node1 att="test att val node 1">test data 1</node1>
<node1 att="test att val node 2">test data 2</node1>
</test>' --query a node set like a table
SELECT xd.rows.value('(.)','nvarchar(50)') AS result
FROM @x.nodes('/test/node1') xd(rows)
--WHERE xd.rows.value('(.)','nvarchar(50)') LIKE '%test data 1'--to get values of attributes and nodes
select @x.value('(/test/@att)[1]', 'int' )--select @x.value('(/test/node1/@att)[1]', 'nvarchar(50)' )
--select @x.value('(/test/node1/@att)[2]', 'nvarchar(50)' )
select @x.value('(/test/node1)[1]', 'nvarchar(50)' )
--select @x.value('(/test/node1)[2]', 'nvarchar(50)' ) --to get nodes/sections/sets/doc fraagments
select @x.query('test/node1')
FYI, the way to dynamically create the xPath statement : use sql:variable to amend the xPath statement - if you try string concats you'll find this out ;-)
Technorati Tags:
SQL,
SQL2005,
SQL2K5,
XPATH,
XML