Using relative paths within SQL XQuery arithmetic

Question

Is there a simple way to use a relative path within XQuery arithmetic within SQL Server?

e.g. with the below code, I'm repeating /a/b/ on both sides of the operator:

declare @x xml = '<a><b><x>10</x><y>20</y></b></a>'
select @x.value('((/a/b/x/text())[1] + (/a/b/y/text())[1])[1]','bigint')

More Info

NB: I'm aware that in the example above I could use the SUM function... Sadly that would not apply to my real use case, where I have multiple elements with various operations to be performed between them.

I'm also familiar with the nodes option to do something like below:

select ab.b.value('((./x)[1] + (./y)[1])[1]','bigint')
from @x.nodes('/a/b') ab(b)

I'm also familiar with using outer apply/cross apply to access such subqueries when the XML is coming from a column rather than a variable. That's currently the route I'm taking, but it feels a little clunky.

I'm visualising a solution similar to this: select @x.value('(/a/b[(./x)[1] + (./y)[1]])[1]','bigint'); i.e. similar to how a filter can be applied to multiple elements within the context of the current path; but haven't found how that would be written (assuming this is even possible).

score 1 · Answer 1 · answered Sep 19 '16 at 21:53

1

In most XQuery systems, given your data, you can replace

'((/a/b/x/text())[1] + (/a/b/y/text())[1])[1]'

with

'/a/b/(x+y)'

But I believe the SQL server implementation has its own quirks so this might not apply in your case. If you need a way to get around pessimistic type checking then

/a/b/sum((x,y))

might do the trick.

answered Sep 19 '16 at 21:53

Michael Kay

156,231
11
92
164

Thanks for your answer. Sadly the first solution gives `Msg 9341, Level 16, State 1, Line 3 XQuery [value()]: Syntax error near '(', expected a step expression. `, and the second : `Msg 9335, Level 16, State 1, Line 3 XQuery [value()]: The XQuery syntax '/function()' is not supported. ` – JohnLBevan Sep 20 '16 at 12:17
Looks like it's supporting an XQuery subset that's actually closer to XPath 1.0. – Michael Kay Sep 20 '16 at 16:24

score 1 · Accepted Answer · answered Sep 21 '16 at 08:08

declare @x xml
SET @x = '<a><b><x>10</x><y>20</y></b></a>'

select @x.query('
    for $i in /a/b
    return
        data($i/x[1]) + data($i/y[1])
')

If the path is too long and you want to use an "alias" for it, the above can be an option. It may look nicer a bit? You can replace + by other operators such as -, *,etc. (tested with SQL server 2005)

Using relative paths within SQL XQuery arithmetic

2 Answers2