MikeHouston.net

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 ;-)