CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
roman
roman

Reputation: 117485

All data from xml with path to node

I'm trying to get all data from xml into strings in SQL Server.

so suppose I have xml like this:

<node1>
  <node2>
    <node3 att1="1">
      456
    </node3>
    <node4 att2="25"/>
  </node2>
</node1>

what I want is to get data like this:

╔══════════════════════════╦════════════╗
║                     Name ║      Value ║
╠══════════════════════════╬════════════╣
║ node1/node2/node3        ║        456 ║
║ node1/node2/node3/@att1  ║          1 ║
║ node1/node2/node3/@att2  ║         25 ║
╚══════════════════════════╩════════════╝

I don't remember XPath well enough, I can do it with recursive query (SQL FIDDLE):

declare @data xml

set @data = '<root><node2><node3 att1="1">ggf</node3><node4 att2="25"/></node2></root>'

;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

how do you think, what is the best way to do this task?

Upvotes: 1

Views: 1519

Answers (1)

JLRishe
JLRishe

Reputation: 101730

Here's a considerably more concise solution than the one in Tony Hopkinson's comment:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/">
    <items>
      <xsl:apply-templates 
             select="(//* | //@*)[text()[normalize-space()] or
                                  not(self::*) and normalize-space()]" />
    </items>
  </xsl:template>

  <xsl:template match="@* | node()">
    <item value="{normalize-space()}">
      <xsl:attribute name="path">
        <xsl:apply-templates select="ancestor-or-self::node()[parent::node()]" 
                             mode="path" />
      </xsl:attribute>
    </item>
  </xsl:template>

  <xsl:template match="@* | node()" mode="path">
    <xsl:value-of select="concat('/',
                                 substring('@', 1, not(self::*)),
                                 name())"/>
  </xsl:template>
</xsl:stylesheet>

The result when run on your sample input is:

<items>
  <item value="456" path="/node1/node2/node3" />
  <item value="1" path="/node1/node2/node3/@att1" />
  <item value="25" path="/node1/node2/node4/@att2" />
</items>

It should be theoretically possible to use the more concise and intuitive

<xsl:apply-templates select="//*[text()[normalize-space()]] |
                             //@*[normalize-space()]" />

but for some reason this crashes my entire IDE and I can't figure out why.

Upvotes: 2

Related Questions