SQL Server XML.value context node

Question

I give users, of the tool we work on, the possibility to have calculate variables. Values are calculated by a given XPath expression. In .Net, with Saxon library, it is possible to calculate the value by executing the XPath expression in the context of the value to be calculated.

Example XML:

@data = '<Object>
  <Parameters>
    <Parameter Name="a">2</Parameter>
    <Parameter Name="b">5</Parameter>
    <Parameter Name="c"/>
  </Parameters>
</Object>'

Example XPath expression in context of Parameter with name "c":

../Parameter[@Name="a"]/text() + ../Parameter[@Name="b"]/text()

This wil result in 7 ;)

I like to execute the XPath expression on the SQL Server, but have now idea how to change the context node of a XML type?

A possibility would be that I add a XPath in front of the expression that changes the context, but have not found if this is possible. Query could than look like:

/<go to node variable "c" and make this the context node>

../Parameter[@Name="a"]/text() + ../Parameter[@Name="b"]/text()

Does someone have good ideas?

Gottfried Lesigang · Accepted Answer · 2016-02-01 23:12:40Z

What you want is - AFAIK - not possible...

Try to define a working query with your XPath expression:

Btw: Your example is wrong in two ways: Attributs within the XML do not have the @-mark and the closing Parameters-tag is missing its final "s"...

DECLARE @data XML=
'<Object>
  <Parameters>
    <Parameter Name="a">2</Parameter>
    <Parameter Name="b">5</Parameter>
    <Parameter Name="c"/>
  </Parameters>
</Object>';

--This will not work, because your parts are not singleton
--SELECT Context.Node.value('../Parameter[@Name="a"]/text() + ../Parameter[@Name="b"]/text()','int')
--FROM @data.nodes('/Object/Parameters/Parameter[@Name="c"]') AS Context(Node);

--This works, because I framed both parts with (...)[1]
SELECT Context.Node.value('(../Parameter[@Name="a"]/text())[1] + (../Parameter[@Name="b"]/text())[1]','int')
FROM @data.nodes('/Object/Parameters/Parameter[@Name="c"]') AS Context(Node);

But there might be a workaround, but it's quite hacky and not reliable:

A Stored Procedure creates a dynamic sql statement built up from your parameters and executes this.

Your must-not-be-changed XPath expression will be changed in the way, that a + with a blank to the left and to the right (as well as -, * and /) is replaced in the way, that there is a singleton expression returned. I do not know how complex calculations might be. Check it out...

CREATE PROCEDURE dbo.CalculateXPathExpression
(
     @data XML
    ,@XPathContextNode VARCHAR(MAX)
    ,@XPathCalculation VARCHAR(MAX)
)
AS
BEGIN
    DECLARE @Return INT;
    DECLARE @CalcSingleton VARCHAR(MAX)='(' + REPLACE(REPLACE(REPLACE(REPLACE(@XPathCalculation,' + ',')[1] + ('),' - ',')[1] + ('),' * ',')[1] + ('),' / ',')[1] + (') + ')[1]';
    DECLARE @Cmd NVARCHAR(MAX)=
    'SELECT @Return = Context.Node.value(''' + @CalcSingleton + ''',''int'')
    FROM @data.nodes(''' +  @XPathContextNode + ''') AS Context(Node);';

    EXEC sp_executesql @Cmd, N'@data XML, @Return INT OUTPUT',@data=@data, @Return=@Return OUTPUT;

    SELECT @Return;

    RETURN;
END
GO

DECLARE @data XML=
'<Object>
  <Parameters>
    <Parameter Name="a">2</Parameter>
    <Parameter Name="b">5</Parameter>
    <Parameter Name="c"/>
  </Parameters>
</Object>';

DECLARE @XPathContextNode VARCHAR(MAX)='/Object/Parameters/Parameter[@Name="c"]';
DECLARE @XPathCalculation VARCHAR(MAX)='../Parameter[@Name="a"]/text() + ../Parameter[@Name="b"]/text()';

EXEC dbo.CalculateXPathExpression @data, @XPathContextNode, @XPathCalculation;
GO

DROP PROCEDURE dbo.CalculateXPathExpression;
GO

Thanks for your answer. You point me to another issue! Saxon XPath is not SQL XPath. I introduced XPath to get my hands free for implementing other functionality instead of making my own language to calculate variables.Another solution could be to use the DLL that calculates the expression on client side also on the server?!

har07 · Accepted Answer · 2016-02-01 03:12:34Z

1

Using SQL Server's XQuery, you can declare XQuery variable, say $p to reference target parent element i.e <Parameters> where child element with attribute Name="c" is found. Then you can do further XPath/XQuery processing while using $p as context element :

declare @data XML = '<Object>
  <Parameters>
    <Parameter Name="a">2</Parameter>
    <Parameter Name="b">5</Parameter>
    <Parameter Name="c"/>
  </Parameters>
</Object>'

SELECT @data.value('
    let $p := /Object/Parameters[Parameter/@Name="c"]
    return ($p/Parameter[@Name="a"])[1] + ($p/Parameter[@Name="b"])[1]
','int') AS Result

sqlfiddle demo

output :

answered Feb 1, 2016 at 3:12

har07

89.5k12 gold badges87 silver badges143 bronze badges

2 Comments

Meindert Oldenburger Over a year ago

Thanks for your answer but this is not what I want. The original XPath should be stay as it is.

Gottfried Lesigang Over a year ago

I voted yours up, because I fance the approach with the "let", didn't know this... Thx!

Collectives™ on Stack Overflow

SQL Server XML.value context node

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related