For example, there is such code:

DECLARE @XML XML='<root> <TableRow> <Name>TT1</Name> </TableRow> <TableRow> <Name>TT2</Name> </TableRow> </root>' DECLARE @Xpath NVARCHAR(255)='root/TableRow' SELECT aevalue('(Name)[1]','nvarchar(255)') FROM @XML.nodes(@Xpath) a(e)

Is it possible to somehow set XPath through a variable, and not explicitly?

It is possible, of course, to generate a query via sp_executesql , but is it possible without it?

    1 answer 1

    You cannot use a variable as an XPath . The documentation on this subject is strict:

    Syntax: nodes (XQuery) as Table (Column)

    Arguments:

    XQuery String Literal ...

    Attempting to do this causes an error Msg 8172 :

    The XML nodes must be a string literal.

    But you can use variables inside XPath .

    For example, instead of

     declare @xml xml = '<nodes><a id="1"/><b id="2"/></nodes>'; declare @path varchar(100); set @path = '/nodes/b'; select xcvalue('@id', 'int') as id from @xml.nodes(@path) x(c); set @path = '/nodes/a'; select xcvalue('@id', 'int') as id from @xml.nodes(@path) x(c);

    can be used

     declare @xml xml = '<nodes><a id="1"/><b id="2"/></nodes>'; declare @nodeName varchar(20); set @nodeName = 'a'; select xcvalue('@id', 'int') as id from @xml.nodes('/nodes[1]/*[local-name()=sql:variable("@nodeName")]') x(c); set @nodeName = 'b'; select xcvalue('@id', 'int') as id from @xml.nodes('/nodes[1]/*[local-name()=sql:variable("@nodeName")]') x(c);