Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

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" ?
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

Open in new window

SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

Ryan - Thanks, but the various boo i:type's may or may not be present, so I can't always assume that "rockstar" will be the second boo, and will need to explicitly query it.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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'

Open in new window

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.