Jim Horn
asked on
SQL Server XML i=Type querying
( An extension of this question.. )
Given the below SQL Server 2008 T-SQL statement and XML fragment, how would I return the value 'Sammy Hagar' WHERE boo i:type="rockstar" ?
Given the below SQL Server 2008 T-SQL statement and XML fragment, how would I return the value 'Sammy Hagar' WHERE boo i:type="rockstar" ?
DECLARE @x XML
SET @x = '<goo xmlns="http://underdog.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<foo>
<boo i:type="fruit">banana</boo>
<boo i:type="rockstar">Sammy Hagar</boo>
<boo i:type="planet">Mars</boo>
<boo i:type="movie">Heathers</boo>
<boo i:type="practicaljoke">Rick Roll</boo>
</foo>
</goo>'
;
WITH XMLNAMESPACES ('http://underdog.com' as d)
SELECT @x.value('(/d:goo/d:foo/d:boo)[1]', 'varchar(64)') AS the_amount
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. -Jim
Here is another version, shifted more to SQL syntax rather the XPath:
DECLARE @x XML
SET @x = '<goo xmlns="http://underdog.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<foo>
<boo i:type="fruit">banana</boo>
<boo i:type="rockstar">Sammy Hagar</boo>
<boo i:type="planet">Mars</boo>
<boo i:type="movie">Heathers</boo>
<boo i:type="practicaljoke">Rick Roll</boo>
</foo>
</goo>'
;
WITH XMLNAMESPACES ('http://underdog.com' as d, 'http://www.w3.org/2001/XMLSchema-instance' as i)
SELECT T.c.value('.','varchar(64)')
FROM @x.nodes('/d:goo/d:foo/d:boo') as T(c)
WHERE T.c.value('@i:type','varchar(64)')='rockstar'
ASKER
Follow up question on .Value attribute querying here.
@zc2 - Thanks for your post, but I had already accepted an answer when you posted it, so if you want to comment in the follow-up question I'll make sure you get some points out of the deal.
@zc2 - Thanks for your post, but I had already accepted an answer when you posted it, so if you want to comment in the follow-up question I'll make sure you get some points out of the deal.
ASKER