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);

Answer 1

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);