title: How to recursively get XPath of a node using SQL Server?
body: <p>I'm tired of looking at possibly the ugliest SQL statement I've ever built and need your help. I am searching through an XML document for various elements and want to see their XPaths. The query below works by brute force, but I cannot come up with a way to create a function or CTE that will properly support N levels.</p>
  
  <pre><code>declare @article xml = '&lt;article&gt;
    &lt;front&gt;
      &lt;article-meta&gt;
        &lt;title-group&gt;
          &lt;article-title&gt;Update on ...&lt;/article-title&gt;
        &lt;/title-group&gt;
      &lt;/article-meta&gt;
    &lt;/front&gt;
    &lt;back&gt;
      &lt;ref-list&gt;
        &lt;ref id="R1"&gt;
          &lt;citation citation-type="journal"&gt;
            &lt;article-title&gt;Retrospective study of ...&lt;/article-title&gt;
          &lt;/citation&gt;
        &lt;/ref&gt;
      &lt;/ref-list&gt;
    &lt;/back&gt;
  &lt;/article&gt;'
  
  SELECT
          Cast(T.r.query('local-name(parent::*/parent::*/parent::*/parent::*/parent::*/parent::*)') AS varchar(max)) + '/' +
          Cast(T.r.query('local-name(parent::*/parent::*/parent::*/parent::*/parent::*)') AS varchar(max)) + '/' +
          Cast(T.r.query('local-name(parent::*/parent::*/parent::*/parent::*)') AS varchar(max)) + '/' +
          Cast(T.r.query('local-name(parent::*/parent::*/parent::*)') AS varchar(max)) + '/' +
          Cast(T.r.query('local-name(parent::*/parent::*)') AS varchar(max)) + '/' +
          Cast(T.r.query('local-name(parent::*)') AS varchar(max)) AS ThePath,
          Cast(T.r.query('local-name(.)') AS varchar(max)) AS TheElement,
          T.r.query('.') AS TheXml
  FROM @article.nodes('//article-title') T(r)
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>ThePath                                 TheElement
  //article/front/article-meta/title-group    article-title
  /article/back/ref-list/ref/citation         article-title
  </code></pre>
  
  <p>What I really want:</p>
  
  <pre><code>SELECT
    x.RowId,
    dbo.GetXPath(T.r.query('.')) AS ThePath, -- &lt;---- Magic function goes here
      T.r.query('.') AS TheXml
  FROM dbo.InputFormatXml x
    JOIN dbo.InputFormat f
      ON F.InputFormatId = x.InputFormatId
  CROSS APPLY TheData.nodes('//article-title') T(r)
  WHERE F.Description = 'NLM';
  </code></pre>