SQL Server 2008 & XQUERY

Question

I'm currently using Xquery in SQL Server to extract some information from XML.

I'm having troubles because I have to put some dynamics content on one variable but it doesn't work.

When I select this :

P.value('ListOrderItem[1]/OrderItem[1]/Item[sql:variable("@I")]/Seller[1]','VARCHAR(64)')

P is my path and it's good because it's working with other items, but I have to do a loop on this item (in one order, you can have many items...), so that's why I want to put the @I and then do a loop on this variable.

PS: Don't say to put the [1] after the variable, else it will select the first item every time.

Edit: By "doesn't work" i mean that it send me only the first item when i put the [1] and error message when i put "[sql:variable("@I")]" which is "value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *"

And of course, i've tried the '+@I' but it still doesn't work..

Example of my XML :

<ListOrderItem>
 <OrderItem>
  <Item>
   ...
  </Item>
  <Item>
   ...
  </Item>
 </OrderItem>
</ListOrderItem>

Final edit : I finally fixed the problem this post helped me a lot, sorry for posting then.

Getting multiple records from xml column with value() in SQL Server

Thanks.

**Show us** a sample XML ! And explain which parts you're trying to fetch from it.... — marc_s, Nov 15 '12 at 16:20
What do you mean by "doesn't work". Error message or wrong data or...? — Mikael Eriksson, Nov 15 '12 at 16:27
I would guess that there is an error saying something about a "singleton". And if that is the case you really do need to add an extra `[1]`. Embed your entire expression in parenthesis and add the `[1]` to the end. — Mikael Eriksson, Nov 15 '12 at 16:32
Even with the variable `@I`, if I put the `[1]`, it select only the first item of each orders — user1827115, Nov 15 '12 at 16:51

score 0 · Answer 1 · answered Nov 15 '12 at 15:43

0

Try this...

select v.x.value('.','varchar(20)')
from
   p.nodes('ListOrderItem/OrderItem//Seller[1]') v(x)

answered Nov 15 '12 at 15:43

podiluska

50,950
7
98
104

I tried it and the error message said that the strings is not on a right format, thanks. – user1827115 Nov 15 '12 at 16:41

score 0 · Answer 2 · answered Nov 15 '12 at 17:05

Embed your entire expression in parenthesis and add the [1] to the end.

declare @XML xml = '
<ListOrderItem>
 <OrderItem>
  <Item>
   <Seller>1</Seller>
  </Item>
  <Item>
   <Seller>2</Seller>
  </Item>
 </OrderItem>
</ListOrderItem>'


declare @I int = 2

select @XML.value('(ListOrderItem[1]/OrderItem[1]/Item[sql:variable("@I")]/Seller[1])[1]','VARCHAR(64)')

Result: 2

SQL Server 2008 & XQUERY

2 Answers2