CODEWITHSUNDEEP
sql-servert-sqlxpath
livnt
livnt

Reputation: 45

Combine variables in SQL Server XPath expression

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?

Upvotes: 1

Views: 161

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28809

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')

Upvotes: 1

Related Questions