title: SQL XML Parsing Query for Element Hierarchy
body: <p>I am attempting to write a SQL Query that will take in an XML object of undefined schema (YAY!) and transform it to a two column table of <code>ElementName</code>, <code>Value</code> columns.  I was able to get a simple query down after some time (I am not a SQL person by any means). </p>
  
  <pre><code>DECLARE @strXml XML
  SET @strXml = '&lt;xml&gt;
    &lt;FirstName&gt;TEST&lt;/FirstName&gt;
    &lt;LastName&gt;PERSON&lt;/LastName&gt;
    &lt;DOB&gt;1/1/2000&lt;/DOB&gt;
    &lt;TestObject&gt;
      &lt;SomeProperty&gt;CHECKED&lt;/SomeProperty&gt;
      &lt;EmbeddedObject&gt;
          &lt;SomeOtherProperty&gt;NOT CHECKED&lt;/SomeOtherProperty&gt;
      &lt;/EmbeddedObject&gt;
    &lt;/TestObject&gt;
  &lt;/xml&gt;'
  
  DECLARE @XmlMappings TABLE
  (        
      NodeName VARCHAR(64),
      Value VARCHAR(128)
  ) 
  INSERT INTO @XmlMappings
  SELECT doc.col.value('fn:local-name(.)[1]', 'varchar(64)') AS ElementName,
         doc.col.value('.', 'varchar(128)') AS Value
  FROM @strXml.nodes('/xml/*') doc(Col)
  SELECT * FROM @XmlMappings
  </code></pre>
  
  <p>This query can handle the simple condition of the specified XML with only the first level elements.  However elements such as TestObject and EmbeddedObject end up flattened.  What I am looking for is to get some type of mapping like </p>
  
  <pre><code>ElementName                                 | Value
  =====================================================
  FirstName                                   | TEST
  LastName                                    | PERSON
  DOB                                         | 1/1/2000
  TestObject.SomeProperty                     | CHECKED
  TestObject.EmbeddedObject.SomeOtherProperty | NOT CHECKED
  </code></pre>
  
  <p>The hard part for me is the hierarchical structure with the . operator.  I don't care if it is some other delimiter than . that gets output, it is more of just getting the output done, and I don't know enough about XML in SQL to be able to know even what to query.</p>
  
  <p>Please note that I can also not use OPENXML since this is looking to be deployed on SQL Azure which does not support that feature at this time.</p>