title: Getting data from poorly structured xml from sql server 2014
body: <p>Hello I have a newbie question, apologies.</p>
  
  <p>I have to work with a stupidly designed XML format 
  that provides object attribute data as tags and where a 
  tag value changes it implies an attribute.
  Where an attribute is not provided eg. mammal, it implies
  that the value is unknown.</p>
  
  <p>Using the following as an example:
  3rd level tag can be one of 20 different values.
  4th level tag (mammal) can be one of several values and
  may not exist.</p>
  
  <p>How can I write an XQuery command to get beyond an 
  unknown/missing tag?</p>
  
  <p>select
         t.c.value('v[1]', 'varchar(100)') as animal
  from @xml.nodes('/recs/rec/<em>/a1/</em>/ as t.c</p>
  
  <pre><code>declare @xml xml =
  '
  &lt;recs&gt;
          &lt;rec&gt;
          &lt;a&gt;
              &lt;a1&gt;
                  &lt;mammal&gt;
                      &lt;v&gt;dog&lt;/v&gt;
                  &lt;/mammal&gt;
              &lt;/a1&gt;
          &lt;/a&gt;
      &lt;/rec&gt;
      &lt;rec&gt;
          &lt;b&gt;
              &lt;a1&gt;
                  &lt;mammal&gt;
                      &lt;v&gt;cat&lt;/v&gt;
                  &lt;/mammal&gt;
              &lt;/a1&gt;
          &lt;/b&gt;
      &lt;/rec&gt;
      &lt;rec&gt;
          &lt;b&gt;
              &lt;a1&gt;
                  &lt;v&gt;pig&lt;/v&gt;
              &lt;/a1&gt;
          &lt;/b&gt;
      &lt;/rec&gt;
  &lt;/recs&gt;'
  </code></pre>
  
  <p>Thanks</p>