querying xml…

December 29th, 2009

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)

In: Uncategorized | | #

4 Comments, Comment or Ping

Jonathan Kehayias

The query you want should be:

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

December 29th, 2009
Mladen Prajdic

SELECT C2.query(‘.’).value(‘.’, ‘varchar(max)’)
FROM @xmlString.nodes(‘a’) AS T1(C1)
cross apply T1.C1.nodes(‘product-id’) as T2(C2)

December 29th, 2009
Arlene

See, I had tried something similar, it’s the ‘.’ that I was missing!!

Thank you! Thank you! Thank you!

Days I had been working on that!

Now what if I wanted to get the promotion-id… PL10ONL

so two columns

PL10ONL bar1
PL10ONL bar2
PL10ONL foo3
PL10ONL bar4

working on that now.

December 29th, 2009
Arlene

got it!! See above… go me. :)

December 29th, 2009