Is there a local-path function similar to local-name?

Question

is there a way to retrieve the full path of a given node, in the way that I can retrieve its name?

declare @x xml; set @x='<ROOT><a>111</a></ROOT>'
SELECT @x.value('local-name((/ROOT/a)[1])', 'varchar(256)')

the above will return 'a'. how do I return '/ROOT/a'?

---

Do what thou wilt shall be the whole of the law -- AL. I. 40

Answer 1

Jacob Sebastian did something on that.  Try here:

http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Answer 2

From SQL 2008 onwards you could do a FLWOR with let:

declare @x xml; 

set @x='<ROOT><a>111</a></ROOT>'

SELECT @x.query('
for $i in //*
let $x := "/"
return concat( local-name($i), $x )').value('.', 'VARCHAR(MAX)')

Answer 3

I'm trying to understand what you've written.  I changed the xml like this:

set @x='<root><a>111</a><blat><aa>xyz</aa><bb>abc</bb></blat></root>'

and when I run your query I get: "root/ a/ blat/ aa/ bb/"... so I don't quite have enough to put it all together.  what I need is to be able to give it a specific node and have the query tell me its path:

SELECT @x.value('what-is-my-path((/root/blat/aa)[1])', 'varchar(256)')

---

Do what thou wilt shall be the whole of the law -- AL. I. 40

Answer 4

oh, I think I see what this is doing: it's enumerating the paths of the children nodes... however, what I need is the path of the given node... so I need to enumerate the ancestors

---

Do what thou wilt shall be the whole of the law -- AL. I. 40

Answer 5

wBob, maybe it would help to give you some background... I posted on SO the problem I was originally trying to solve (see: http://goo.gl/pI4Ap) and once it became clear the answer was not within easy reach I thought I might try something like this:

select y.value('(.)[1]', 'varchar(50)')
from @x.nodes('*') t(c)
cross apply c.nodes('*') x(y)
where y.value('my-path-function(.)', 'varchar(50)') = @somepath

so when I saw your post (not being familiar with FLWOR) I was excited I could get it done after all... and maybe it's still possible.  do you see a way? 

---

Do what thou wilt shall be the whole of the law -- AL. I. 40

Answer 6

Jacob's function kind of does that.  I've posted a simplifed example below based on that.  I've also included some examples using dynamic SQL which is the only way to pass long paths in, plus a simple example using local-name.

declare @xml xml
set @xml = '<root><a>111</a><blat><aa>xyz</aa><bb>abc</bb></blat></root>'

-- CTE to display all paths for all elements
;WITH cte AS
(
SELECT 
	1 AS x,
	x.c.query('.') yourXML,
	x.c.value('.', 'VARCHAR(50)') elementValue,
	x.c.value('local-name(.)', 'VARCHAR(50)') elementName,
	CAST ( NULL AS VARCHAR(MAX) )  elementPath
FROM @xml.nodes('/*') x(c)

UNION ALL

SELECT 
	x + 1,
	n.c.query('*') yourXML,
	n.c.value('.', 'VARCHAR(50)') elementValue,
	n.c.value('local-name(.)', 'VARCHAR(50)') elementName,
	ISNULL( c.elementPath + '/', '' ) + n.c.value('local-name(.)', 'VARCHAR(MAX)') elementPath
FROM cte c
	CROSS APPLY c.yourXML.nodes('*') n(c)
)
SELECT * FROM cte



-- local-name with sql:variable
declare @simplePath nvarchar(20)
set @simplePath = 'bb'
select @xml.query('//*[local-name()=sql:variable("@simplePath")]') bb



-- Dynamic SQL
declare @sql nvarchar(max)
declare @myPath nvarchar(100)

-- Get a element
set @myPath = 'root/a'
set @sql = 'select @xml.query(''' + @myPath +  ''') a'

EXEC sp_executesql @sql, N'@xml XML', @xml


-- Get bb element
set @myPath = '//bb'
set @sql = 'select @xml.query(''' + @myPath +  ''') c'

EXEC sp_executesql @sql, N'@xml XML', @xml

Answer 7

I think I haven't mentioned the container for this code is a UDF, which means I can't run sp_executesql... in fact, if I could this would be a non-issue... looking at your code...

---

Do what thou wilt shall be the whole of the law -- AL. I. 40

Answer 8

that is most excellent.  you've solved my problem.  the complete solution and my thanks to you on my original SO post

---

Do what thou wilt shall be the whole of the law -- AL. I. 40