Combine variables in SQL Server XPath expression

Question

I have this expression on SQL Server which works fine:

xmlData.value('sum(//*[variable1="222"]/./variable3)', 'float')

I want to rewrite it so it contains these three variables:

declare @var1 nvarchar(max) = 'variable1'
declare @var2 int = 222
declare @var3 nvarchar(max) = 'variable3'

Here's some xml relevant to my question:

<main>
  <TAL>
    <variable1>222</variable1>
    <Name>someName1</Name>
    <a>2</a>
    <b>73</b>
    <variable3>1</variable3>
  </TAL>
  <TAL>
    <variable1>333</variable1>
    <Name>someName2</Name>
    <a>4</a>
    <b>56</b>
    <variable3>2</variable3>
  </TAL>
</main>

I have tried several options such as:

xmlData.value('sum(//*[local-name()=sql:variable("@var1")="local-name()=sql:variable("@var2")"]/./local-name()=sql:variable("@var3"))', 'float')

but no success. What would be the proper way to write the XPath expression using variables?

Hi livnt, can you explain in words what it is you are trying to achieve? I can't really make out what the sample xpath query you have in your question is trying to achieve. — TT.
– TT., Commented Aug 12, 2018 at 18:00
Thanks TT, I'm searching an element named "variable1" and its value. If it's 222, I'm getting the sibling of it - element "variable3" value. Problem is that I need to pass the variables as parameters to a function, so the expression needs to contain them — livnt
– livnt, Commented Aug 12, 2018 at 19:37
Hi again livnt. Typically for questions like these, it is handy to include some sample XML for people to toy around with, and the expected result for your sample XPath query for verification purposes. Makes it that much easier for people around here to start with actually answering your question. It is always possible the edit your question to include such a sample, using the edit link underneath the question. — TT.
– TT., Commented Aug 13, 2018 at 5:24
As per your suggestion, the question is now contains some xml of mine. Thank you — livnt
– livnt, Commented Aug 13, 2018 at 6:55

Jeroen Mostert · Accepted Answer · 2018-08-13 07:21:15Z

1

Because we're moving paths into node tests, we'll have to rearrange things a bit. Queries like these lack the convenience of static paths, unfortunately.

select xmlData.value('
    sum(
        //*[local-name()=sql:variable("@var1") and data(.)=sql:variable("@var2")]
            /parent::*/*[local-name()=sql:variable("@var3")]
    )', 'float')

answered Aug 13, 2018 at 7:21

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

livnt Over a year ago

Jeroen, its working! This is exactly what I needed. Thanks a lot!

Gottfried Lesigang Over a year ago

@livnt Just a tiny hint: instead of data(.) I'd use (text()[1] cast as xs:int?) which will enable the comparisson as int-values and you can use /../ instead of /parent::*/ - but that's more a matter of taste...

Collectives™ on Stack Overflow

Combine variables in SQL Server XPath expression

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related