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, Comment or Ping

  1. 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

  2. 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

  3. 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

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

    December 29th, 2009

Reply to “querying xml…”