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
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
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
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
got it!! See above… go me.
December 29th, 2009
Reply to “querying xml…”