xml - How to recursively get XPath of a node using SQL Server? -

xml - How to recursively get XPath of a node using SQL Server? -

i'm tired of looking @ perchance ugliest sql statement i've ever built , need help. searching through xml document various elements , want see xpaths. query below works brute force, cannot come way create function or cte back upwards n levels.

declare @article xml = '<article> <front> <article-meta> <title-group> <article-title>update on ...</article-title> </title-group> </article-meta> </front> <back> <ref-list> <ref id="r1"> <citation citation-type="journal"> <article-title>retrospective study of ...</article-title> </citation> </ref> </ref-list> </back> </article>' select cast(t.r.query('local-name(parent::*/parent::*/parent::*/parent::*/parent::*/parent::*)') varchar(max)) + '/' + cast(t.r.query('local-name(parent::*/parent::*/parent::*/parent::*/parent::*)') varchar(max)) + '/' + cast(t.r.query('local-name(parent::*/parent::*/parent::*/parent::*)') varchar(max)) + '/' + cast(t.r.query('local-name(parent::*/parent::*/parent::*)') varchar(max)) + '/' + cast(t.r.query('local-name(parent::*/parent::*)') varchar(max)) + '/' + cast(t.r.query('local-name(parent::*)') varchar(max)) thepath, cast(t.r.query('local-name(.)') varchar(max)) theelement, t.r.query('.') thexml @article.nodes('//article-title') t(r)

result:

thepath theelement //article/front/article-meta/title-group article-title /article/back/ref-list/ref/citation article-title

what want:

select x.rowid, dbo.getxpath(t.r.query('.')) thepath, -- <---- magic function goes here t.r.query('.') thexml dbo.inputformatxml x bring together dbo.inputformat f on f.inputformatid = x.inputformatid cross apply thedata.nodes('//article-title') t(r) f.description = 'nlm';

declare @idoc int; exec sp_xml_preparedocument @idoc output, @article; select isnull(id,'') id, parentid, localname #nodetree openxml(@idoc,'/',3) nodetype = 1; exec sp_xml_removedocument @idoc; alter table #nodetree add together primary key (id); cte ( select parentid ,cast('/' varchar(max)) + localname xpath #nodetree localname = 'article-title' union select parent.parentid ,cast('/' varchar(max)) + localname + xpath cte node inner bring together #nodetree parent on parent.id = node.parentid ) select xpath cte parentid null

sql-server xml xpath sqlxml