4

I am new to xquery in SQL Server.

I have often come across xquery expressions using [1] with attributes.

Can somebody please explain what does it mean?

Here is a example 

declare @aa xml
set @aa='<data>
  <row>
    <Value>1</Value>
    <Text>Masters</Text>
  </row>
  <row>
    <Value>2</Value>
    <Text>Transactions</Text>
  </row>
  <row>
    <Value>3</Value>
    <Text>Misch. Reports</Text>
  </row>
</data>'


select a.f.value('Value[1]','varchar(50)'),   --  why [1] here ?
   a.f.value('Text[1]','varchar(50)')         --  and here too..
 from @aa.nodes('/data/row') as a(f)

Thanks n Regards

Deb
  • 981
  • 13
  • 39
  • Good Q - I always assumed it was an index (first occurrence of the `Value` node) but I'm not sure – JNK Apr 09 '12 at 20:49
  • certainly it is not an index otherwise it wont return multiple rows – Deb Apr 09 '12 at 20:50
  • Well the answer seems to indicate otherwise :) Bear in mind this is a hierarchy, so I think it means the first value node at that level of the tree. You multiple rows so you get multiple values. – JNK Apr 09 '12 at 20:52
  • 2
    It is an index for the current element. You did `from @aa.nodes('/data/row') as a(f)` so for each row you basically have `.` (or my current element) equal to a `row` element. If that `row` element had two `Text` elements, you could get the value of the second one with `Text[2]` which is equivalent to `./Text[2]`. – Jeremy Pridemore Apr 09 '12 at 20:53

2 Answers2

9

In XPath the [expression] syntax denotes a predicate on the location path. [1] is the abbreviated syntax for [position()=1], which means 'the first element'. In SQL Server use of XPath the [1] (or any other predicate that deterministically filters to at most one element) is required because it transforms the XPath expression from one that returns any number of elements to one that deterministically returns 0 or 1 elements, thus transforming into a scalar expression, which is what .value() requires:

The XQuery must return at most one value.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
3

In this case you're saying you want the first Value element for the current /data/row and the first Text element for the same. If you put a [2] there it will mean the second one. By putting a [1] even where you know there will be only one row, you make it feel safe that only one element will enter the value function.

Deb
  • 981
  • 13
  • 39
Jeremy Pridemore
  • 1,995
  • 1
  • 14
  • 24
  • Is that all ? no other reason ? – Deb Apr 09 '12 at 20:55
  • What do you mean? Yes, that is all. It is because you could have `679` and want to get the third `Data` element in your query. So the `[#]` syntax allows it. – Jeremy Pridemore Apr 09 '12 at 20:57
  • i am almost convinced but still waiting if some one have any more info. – Deb Apr 09 '12 at 21:00
  • Then look at Remus' answer, which is more thorough and has references. Consider marking that one the answer, as it says the same thing with more detail and better. :) *EDIT: looks like I spoke too slow* – Jeremy Pridemore Apr 09 '12 at 21:03