T-SQL to find a node within a dynamic XML structure

Question

I would like to know what is the correct way to write a T-SQL query to return either the value or attribute of a node from a dynamic (multilevel) XML structure?

For example, if the XML looked like this:

<xml>
  <a>
    <b>1</b>
    <c>
      <node attrib="ant">2</node>
    </c>
  </a>
  <a>
    <node attrib="bird">3</node>
  </a>
  <a>
    <b>
      <c>
        <node attrib="cat">4</node>
      </c>
    </b>
  </a>
</xml>

What is the correct query to return the values and/or the attributes for node? As shown in this example, node can be at any level...

I've tried something like this (without success):

SELECT
  node.value('node[1]', 'varchar(50)') AS node
  node.value('(node/@attrib)[1]', 'varchar(50)') AS attrib
FROM
  xml.nodes('//xml/*') AS xml(node)

EDIT: Thanks StuartLC for the answer below...

Based on the help below, here is a working example which also contains namespaces:

DECLARE @xml XML;
SET @xml = '
<xml xmlns:abc="xyz">
    <a>
        <b>1</b>
        <c>
            <abc:node attrib="ant">2</abc:node>
        </c>
    </a>
    <a>
        <abc:node attrib="bird">3</abc:node>
    </a>
    <a>
        <b>
            <c>
                <abc:node attrib="cat">4</abc:node>
            </c>
        </b>
    </a>
</xml>';

;WITH XMLNAMESPACES('xyz' AS ns)
SELECT
    Nodes.node.value('.', 'varchar(50)') AS node,
    Nodes.node.value('(./@attrib)[1]', 'varchar(50)') AS attrib
FROM
    @xml.nodes('//ns:node') AS Nodes(node);

StuartLC · Accepted Answer · 2014-04-26 06:19:41Z

2

Like so:

SELECT
  Nodes.node.value('.', 'varchar(50)') AS node,
  Nodes.node.value('(./@attrib)[1]', 'varchar(50)') AS attrib
FROM
  @xml.nodes('//node') AS Nodes(node);

Because it seems that your node elements can sit at any place in your xml document, the // is used to search all elements. Once each node is found, current() or just . can be used to access this node.

SqlFiddle here

Edit: For parsing an xml column off a table (as opposed to an Xml @variable):

 SELECT
  Nodes.node.value('.', 'varchar(50)') AS node,
  Nodes.node.value('(./@attrib)[1]', 'varchar(50)') AS attrib
FROM
    [TableWithXmlColumn] xyz
    cross apply xyz.XmlCol.nodes('//node') as Nodes(node);

edited Apr 26, 2014 at 6:19

answered Jan 14, 2014 at 10:40

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

Thanks. For a "simple" structure your query works great! :) If I complicate things a little more by adding in a namespace, how would the code look then? For example: sqlfiddle.com/#!3/68b32/373

Mikael Eriksson · Accepted Answer · 2014-01-14 12:03:09Z

2

If you want the values in the same column you can use something like this:

select T.X.value('.', 'varchar(50)')
from @XML.nodes('//*/text(), //@*') as T(X)

SQL Fiddle

edited Jan 14, 2014 at 12:03

answered Jan 14, 2014 at 10:50

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

T-SQL to find a node within a dynamic XML structure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related