I got a question the other day about grabbing node names and values from an XML document. The person asking was using string parsing with the SUBSTRING function to split out names from content based on '>' characters in the XML string. Not a fullproof solution, to be sure, but quick and easy based on his predictable XML data.  So the question then becomes is there an XML-based method to grab this information? 

DECLARE @x xml;
SET @x = N'<?xml version = "1.0"?>
<root>
  <person>
    <name>
      <first-name>Joe</first-name>
      <last-name>Johnson</last-name>
    </name>
    <name>
      <first-name>Lennox</first-name>
      <last-name>Lewis</last-name>
    </name>
    <name>
      <first-name>Louie</first-name>
      <last-name>McDougal</last-name>
    </name>
  </person>
</root>';

SELECT node.query('fn:local-name(.)') AS NodeName, node.query('./text()') AS NodeValue
FROM @x.nodes(N'//*') T(node);

The fn:local-name() function retrieves the local name portion of XML node names, and the text() node test retrieves the node text, as shown below.