December 29th, 2009

querying xml…


I need my query to return:

bar1
bar2
foo3
bar4

Here is what I have so far… I also tried to use a while statement to loop through… no luck(any ideas?):

DECLARE @xmlString XML;

SET @xmlString = N’

<a promotion=”PL10ONL”>
<product-id>bar1</product-id>
<product-id>bar2</product-id>
<product-id>foo3</product-id>
<product-id>bar4</product-id>
</a>
‘

SELECT ISNULL(T.lala.value(‘./product-id[1]‘,‘varchar(20)’),‘found nothing’)
FROM @xmlString.nodes(‘a’) AS T(lala);

SELECT ISNULL(T.lala.value(‘./product-id[2]‘,‘varchar(20)’),‘found nothing’)
FROM @xmlString.nodes(‘a’) AS T(lala);

/*====================================*/

edit post:

Got it!!! Joy!


DECLARE @xmlString XML;

SET @xmlString = N’
<a promotion=”PL10ONL”>
<product-id>bar1</product-id>
<product-id>bar2</product-id>
<product-id>foo3</product-id>
<product-id>bar4</product-id>
</a>’

SELECT s.lala.value(‘@promotion’, ‘varchar(20)’),T.lala.value(‘.’, ‘varchar(20)’)
FROM @xmlString.nodes(‘a/product-id’) AS T(lala)
OUTER apply @xmlString.nodes(‘a’) AS S(lala)

4 Comments | In: Uncategorized | | #