Xquery - How to Use the SQL:Variable in 'Value()' Function

XQUERY - How to use the sql:variable in 'value()' function?

declare @T table(XMLCol xml)
insert into @T values
('<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <DisplayName type="string" display="Display name">No this</DisplayName>
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <children>
        <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
        <HiddenName type="string" display="Hidden name">GMT</HiddenName>
      </children>
    </TimeZone>
  </Elem1>
</Root>') 

declare @Node1 varchar(50)
set @Node1 = 'TimeZone'

declare @Node2 varchar(50)
set @Node2 = 'DisplayName'

select N2.Value.value('.', 'varchar(100)') as Value 
from @T as T
  cross apply (select T.XMLCol.query('//*[local-name()=sql:variable("@Node1")]')) as N1(Value) 
  cross apply (select N1.Value.query('//*[local-name()=sql:variable("@Node2")]')) as N2(Value)

Supply xml element value in modify() method

You are not allowed to use variables as part of the XPath, but you can use a predicate:

DECLARE @xml XML=
N'<root>
    <optional>
        <educational>SomeText</educational>
        <someOther>blah</someOther>
    </optional>
  </root>';

--The straight approach as you know it:

SET @xml.modify('replace value of (/root/optional/educational/text())[1] with "yeah!"');

SELECT @xml;

--Now we use a variable to find the first node below <optional>, which name is as given:

DECLARE @ElementName VARCHAR(100)='educational';

SET @xml.modify('replace value of (/root/optional/*[local-name()=sql:variable("@ElementName")]/text())[1] with "yeah again!"');

SELECT @xml;

Try it out...

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

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

Use xQuery to extract attribute values that also exist in SQL variable

You can use contains function.
Declare a variable like so:

DECLARE @ATTRIBUTES VarChar(Max) = '|AAA|BBB|CAR|XYZ|';

And in you query instead of @name = "AAA" use:

contains(sql:variable("@ATTRIBUTES"), concat("|", @name, "|"))

XQuery/XPath uses sql parameter?

declare @para varchar(10) = 'b';
declare @x xml = '
<x>
    <a>1111</a>
    <b>2222</b>
    <c>3333</c>
</x>';
select @x.query('/x/*[local-name()=sql:variable("@para")]');

local-name()' requires a singleton (or empty sequence) T-SQL Xquery

One more set of parenthesis gives you a singleton as required:

DECLARE @t TABLE(s XML);
INSERT INTO @t(s)VALUES(N'<s1:DB2Request xmlns:s1="XYZDB2">
  <s1:sync>
    <s1:after identityInsert="false">
      <s1:PG204AT5>
        <s1:ISA06>1975111</s1:ISA06>
           <somethingsomething/>
      </s1:PG204AT5>
    </s1:after>
  </s1:sync>
</s1:DB2Request>');

select 
    s.value(
        'local-name((//*[local-name()="after"][1]/*)[1])',  
        'varchar(32)'
    ) as XmlTableName
from @t;

Results in:

XmlTableName
PG204AT5

SQL REPLACE function inside Xml.modify 'replace value of'

_{Note: this answer assumes you want to have this formatted for the purpose of displaying this as a string, and not parsing the content as a xs:dateTime. If you want the latter, Shungo's answer will format it as such.}

---

It seems that replace is not a supported XQuery function in SQL Server at the time of this writing. You can use the substring function along with the concat function in a "replace value of (XML DML)" though.

CREATE TABLE #t(x XML);
INSERT INTO #t(x)VALUES(N'<root><id>1</id><timestamp>16-10-2017 19:24:55</timestamp></root>');

UPDATE
    #t
SET
    x.modify('replace value of (/root/timestamp/text())[1]
              with concat(substring((/root/timestamp/text())[1],1,2),
                          "/",
                          substring((/root/timestamp/text())[1],4,2),
                          "/",
                          substring((/root/timestamp/text())[1],7)
                         ) ') 

SELECT*FROM #t;

Giving as a result:

<root><id>1</id><timestamp>16/10/2017 19:24:55</timestamp></root>

---

Related Topics