title: All data from xml with path to node
body: <p>I'm trying to get all data from xml into strings in SQL Server.</p>
  
  <p>so suppose I have xml like this:</p>
  
  <pre><code>&lt;node1&gt;
    &lt;node2&gt;
      &lt;node3 att1="1"&gt;
        456
      &lt;/node3&gt;
      &lt;node4 att2="25"/&gt;
    &lt;/node2&gt;
  &lt;/node1&gt;
  </code></pre>
  
  <p>what I want is to get data like this:</p>
  
  <pre><code>╔══════════════════════════╦════════════╗
  ║                     Name ║      Value ║
  ╠══════════════════════════╬════════════╣
  ║ node1/node2/node3        ║        456 ║
  ║ node1/node2/node3/@att1  ║          1 ║
  ║ node1/node2/node3/@att2  ║         25 ║
  ╚══════════════════════════╩════════════╝
  </code></pre>
  
  <p>I don't remember XPath well enough, I can do it with recursive query (<a href="http://sqlfiddle.com/#!6/d41d8/5134" rel="nofollow"><strong>SQL FIDDLE</strong></a>):</p>
  
  <pre><code>declare @data xml
  
  set @data = '&lt;root&gt;&lt;node2&gt;&lt;node3 att1="1"&gt;ggf&lt;/node3&gt;&lt;node4 att2="25"/&gt;&lt;/node2&gt;&lt;/root&gt;'
  
  ;with
  CTE_xpath as (
    select
      T.C.value('local-name(.)', 'nvarchar(max)') as Name,
      T.C.query('./*') as elements,
      T.C.value('text()[1]', 'nvarchar(max)') as Value
    from @data.nodes('*') as T(c)
  
    union all
  
    select
      p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') as Name,
      T.C.query('./*') as elements,
      T.C.value('text()[1]', 'nvarchar(max)') as Value
    from CTE_xpath as p
      cross apply p.elements.nodes('*') as T(C)
    union all
  
    select
      p.Name + '/' +
      T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
      T.C.value('local-name(.)', 'nvarchar(max)') as Name,
      null as elements,
      T.C.value('.', 'nvarchar(max)') as Value
    from CTE_xpath as p
      cross apply p.elements.nodes('*/@*') as T(C)
  )
  select Name, Value
  from CTE_xpath
  where Value is not null
  </code></pre>
  
  <p>how do you think, what is the best way to do this task?</p>