Given this scenario, I selected the nodes with xpath //client-agent that matches three nodes:

• First at /root/other/client-agent
• Second at /root/other/other/client-agent
• Third at /root/other/other/client-agent too
• Fourth at /root/other/other/client-agent/client-agent

begin
    declare @Xml xml

    set @Xml = convert(xml,'<root><other><client-agent type="a" /><other><client-agent type="b" /><client-agent type="c" ><some-info /></client-agent></other></other></root>')

    select @Xml

    select t.n.value('@type','varchar(max)') as [client-agent@type]
         , t.n.query('.') OuterXml
         , 'Is there a "t.n.SomeXmlFunction(WithTheRequiredParameters)" expression to get the node path' as [the-path-i-want-to-get]
      from @Xml.nodes('//client-agent') as t(n)
end

Is there an expression ( like native .query() or .value('','') ) on the node t.n to get the xpath on the column [the-path-i-want-to-get] as I described in the list above the code?

Given this scenario, I selected the nodes with xpath //client-agent that matches three nodes:

• First at /root/other/client-agent
• Second at /root/other/other/client-agent
• Third at /root/other/other/client-agent too
• Fourth at /root/other/other/client-agent/client-agent

begin
    declare @Xml xml

    set @Xml = convert(xml,'<root><other><client-agent type="a" /><other><client-agent type="b" /><client-agent type="c" ><some-info /></client-agent></other></other></root>')

    select @Xml

    select t.n.value('@type','varchar(max)') as [client-agent@type]
         , t.n.query('.') OuterXml
         , 'Is there a "t.n.SomeXmlFunction(WithTheRequiredParameters)" expression to get the node path' as [the-path-i-want-to-get]
      from @Xml.nodes('//client-agent') as t(n)
end

Is there an expression ( like native .query() or .value('','') ) on the node t.n to get the xpath on the column [the-path-i-want-to-get] as I described in the list above the code?

• sql-server
• t-sql
• xpath
• xquery

1 Answer 1

Unfortunately there is no easy way to get this in SQL Server.

You can use some XQuery to descend through all the nodes in the XML, checking for the context node, and return node names as you go. This is likely to be very inefficient for a large number of nodes.

This would have been much easier had SQL Server supported the path-to-node-with-pos function, or even the ancestor:: axis.

The logic is:

• Set $current to the context node.
• Loop through all descendant nodes from the top of the XML.
  • If that node has any descendant node (incl. itself) which...
    • ... is the identical node as $current
• Then return / plus the name of that node.
• We need to use .query to return all those text nodes, then .value to munge them all into one string.

select
  t.n.value('@type','varchar(max)') as [client-agent@type],
  t.n.query('.') OuterXml,
  t.n.query('
    let $current := .
    for $i in //* [ descendant-or-self::node() [. is $current ] ]
    return concat("/", local-name($i))
  ').value('text()[1]', 'nvarchar(max)') as [the-path-i-want-to-get]
from @Xml.nodes('//client-agent') as t(n);

This doesn't return the position of each of those nodes. For that you need an even more complex XQuery, which can't use the position(.) function either. SO we need to count all previous matching nodes.

The logic of the count() is:

• From the $i loop variable, ascend to the .. parent, then get all descendant nodes where...
• ... the name of that node is the same as the name of $i
• ... and it is earlier << in the document than $i.
• And add 1.

select
  t.n.value('@type','varchar(max)') as [client-agent@type],
  t.n.query('.') OuterXml,
  t.n.query('
    let $current := .
    for $i in //* [ descendant-or-self::node() [. is $current ] ]
    let $name := local-name($i)
    let $c := count( $i/../* [local-name(.) eq $name and . << $i] ) + 1
    return concat("/", $name, "[", string($c), "]")
  ').value('text()[1]', 'nvarchar(max)') as [the-path-i-want-to-get]
from @Xml.nodes('//client-agent') as t(n);

db<>fiddle

On very large documents it may be faster to use some kind of recursive CTE.