SQL - Using a variable as the entire XQuery path in XML.value()

Question

Is it possible to use a variable to define the entire XQuery path when using .value() on an XML field?

Using [local-name()=sql:variable("@FilterA")], I can define the filter I want to apply as a pair of variables, but I can't get a valid syntax for the entire XQuery path to work.

Example

DECLARE @myData AS TABLE (myID INT, Parameter XML)
INSERT INTO @myData VALUES
(1, '<paramdata><Date>19/06/15</Date><term>1</term></paramdata>'),
(2, '<paramdata><Date>19/06/15</Date><term>5</term></paramdata>')

--This works as expected
SELECT * FROM @myData
WHERE Parameter.value('/paramdata/term=5','bit') = 1


--Two variables is possible
DECLARE @FilterA VARCHAR(255) = 'term'
DECLARE @FilterB VARCHAR(255) = '5'

SELECT * FROM @myData
WHERE Parameter.value('(/paramdata/*[local-name()=sql:variable("@FilterA")])[1]','int') = @FilterB

--but a single variable isn't
DECLARE @Filter1 VARCHAR(255) = '/paramdata/term=5'
SELECT * FROM @myData
WHERE Parameter.value('[local-name()=sql:variable("@Filter1")]','bit') = 1

I'm aware that casting the whole query to a string and "EXEC"-ing it should work, but this likely not suitable for the larger case where I want to apply this.

EDIT

After reading about the XY problem this question should be rephrased as:
"is there a way to use an argument of the format SomeNode=SomeValue to filter on XML columns?"

Is the structure always the same, or is this just an example? — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 14, 2016 at 10:35
Btw: How is this XML generated? Date values should be ISO8601 and never an ambigous culture specific string format... — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 14, 2016 at 10:36
More or less the same - there will always be a <paramdata/> element, but the inner nodes may vary, so date, term etc may not exist on some records. Point taken on the date values - consider this a work in progress... — calvinXS
– calvinXS, Commented Oct 14, 2016 at 10:42
If there is always the node paramdata why would you want to pass in the filter as /paramdata/SomeNode=SomeValue? Was it possible to pass in SomeNode=SomeValue? — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 14, 2016 at 10:47
Btw: This is a good question. Clearly explained. copy'n'paste-sample code... Worth an upvote! — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 14, 2016 at 10:58

Community · Accepted Answer · 2017-03-20 10:29:30Z

3

As pointed out, it is absolutely impossible to use a variable path other than with dynamic sql and EXEC.

But you might do something like this:

DECLARE @myData AS TABLE (myID INT, Parameter XML)
INSERT INTO @myData VALUES
(1, '<paramdata><Date>19/06/15</Date><term>1</term></paramdata>'),
(2, '<paramdata><Date>19/06/15</Date><term>5</term></paramdata>')

DECLARE @Filter1 VARCHAR(255) = 'term=5';

WITH Splitted AS
(
    SELECT LEFT(@Filter1,CHARINDEX('=',@Filter1)-1) AS NodeName
          ,RIGHT(@Filter1,CHARINDEX('=',REVERSE(@Filter1))-1) AS SearchValue
)
SELECT md.myID
      ,md.Parameter
      ,md.Parameter.value('(/paramdata/*[local-name()=sql:column("NodeName")])[1]','nvarchar(max)')
FROM Splitted
CROSS APPLY @myData AS md

(You can use this expression in a WHEREclause as well

Another approach might be this:

DECLARE @myData AS TABLE (myID INT, Parameter XML)
INSERT INTO @myData VALUES
(1, '<paramdata><Date>19/06/15</Date><term>1</term></paramdata>'),
(2, '<paramdata><Date>19/06/15</Date><term>5</term></paramdata>')

DECLARE @Filter1 VARCHAR(255) = 'term=5';

WITH TheRightID AS
(
    SELECT md.myID
    FROM  @myData AS md     
    CROSS APPLY md.Parameter.nodes('/paramdata/*') AS A(Nd)
    WHERE Nd.value('local-name(.)','nvarchar(max)') + N'=' + Nd.value('.','nvarchar(max)')=@Filter1
)
SELECT * FROM @myData WHERE myID IN(SELECT x.myID FROM TheRightID AS x)

EDIT

You might even use this to fully answer your initial question:

    WHERE N'/' + Nd.value('local-name(..)','nvarchar(max)') 
        + N'/' +Nd.value('local-name(.)','nvarchar(max)') 
        + N'=' + Nd.value('.','nvarchar(max)')=@Filter1

Advise

But my advise was to read about the XY-problem :-)

edited Mar 20, 2017 at 10:29

CommunityBot

11 silver badge

answered Oct 14, 2016 at 10:56

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

calvinXS Over a year ago

Yes, exactly! These solutions solve the problem I have, rather than the answer I proposed (to which @Sergey has answered correctly - "nope").

serges_newj · Accepted Answer · 2016-10-14 10:09:32Z

1

It is not possible to use dynamic XQuery path. The path parameter of .value() method and others is considered as string literal, not as variable.

answered Oct 14, 2016 at 10:09

serges_newj

8381 gold badge15 silver badges26 bronze badges

4 Comments

calvinXS Over a year ago

The two-part solution seems to suggest that introducing variables in a string literal using "sql:variable()" is possible.

calvinXS Over a year ago

... WHERE Parameter.value('(/paramdata/*[local-name()=sql:variable("@FilterA")])[1]','int') = @FilterB - but using a variable for the entire argument is where I can't decipher the syntax required - if it is at all possible.

serges_newj Over a year ago

@calvinXS Yes, you can use "sql:variable()" for referring outer variables in expression, but you can't use variables to construct XQuery expression itself. If this were so, there would be no need for "sql:variable()" clause, because it would be possible simply concatenate: '(/paramdata/*[local-name()=' + @F‌ilterA + ')])[1]'

Gottfried Lesigang Over a year ago

@Sergey, fully ACK This answer is a bit to short for a good answer, but there is not much more to say... +1 from my side

Collectives™ on Stack Overflow

SQL - Using a variable as the entire XQuery path in XML.value()

2 Answers 2

EDIT

Advise

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

Advise

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related