none
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

    Monday, September 17, 2012 4:05 PM

Answers

  • 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

    • Proposed as answer by wBobEditor Wednesday, September 19, 2012 11:11 AM
    • Marked as answer by ekkis Wednesday, September 19, 2012 11:27 PM
    Wednesday, September 19, 2012 11:11 AM

All replies

  • Monday, September 17, 2012 8:02 PM
  • 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)')

    • Marked as answer by ekkis Wednesday, September 19, 2012 1:41 AM
    • Unmarked as answer by ekkis Wednesday, September 19, 2012 2:59 AM
    Monday, September 17, 2012 8:14 PM
  • 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

    Wednesday, September 19, 2012 2:27 AM
  • 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

    Wednesday, September 19, 2012 2:42 AM
  • 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


    • Edited by ekkis Wednesday, September 19, 2012 3:04 AM
    Wednesday, September 19, 2012 3:04 AM
  • 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

    • Proposed as answer by wBobEditor Wednesday, September 19, 2012 11:11 AM
    • Marked as answer by ekkis Wednesday, September 19, 2012 11:27 PM
    Wednesday, September 19, 2012 11:11 AM
  • 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

    Wednesday, September 19, 2012 9:34 PM
  • 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

    Wednesday, September 19, 2012 11:27 PM