title: Parsing XML to Columns using SQL Server
body: <p>What is the simplest way to parse XML into columns with SQL Server. Example:</p>
  
  <pre><code>&lt;ns0:root xmlns:ns0="http://herp..."&gt;
      &lt;ns1:FirstElement xmlns:ns1="http://derpyderp..."&gt;
          &lt;ns1:FirstElementID&gt;AAF11303&lt;/ns1:FirstElementID&gt;
          &lt;ns1:FirstEValue&gt;some random text&lt;/ns1:FirstEValue&gt;
          &lt;ns1:SecondElement&gt;
              &lt;ns1:Something&gt;Asdsad&lt;/ns1:Something&gt;
              &lt;ns1:Else&gt;
                  &lt;ns1:Stuff&gt;sdf&lt;/ns1:Stuff&gt;
                  &lt;ns1:StuffVal&gt;15&lt;/ns1:StuffVal&gt;
              &lt;/ns1:Else&gt;
              &lt;ns1:Else&gt;
                  &lt;ns1:Stuff&gt;jarjar&lt;/ns1:Stuff&gt;
                  &lt;ns1:StuffVal&gt;16&lt;/ns1:StuffVal&gt;
                  &lt;ns1:StuffParam&gt;true&lt;/ns1:StuffParam&gt;
              &lt;/ns1:Else&gt;
          &lt;/ns1:SecondElement&gt;
          &lt;ns1:randValue&gt;dosd&lt;/ns1:randValue&gt;
      &lt;/ns1:FirstElement&gt;
      &lt;ns1:FirstElement&gt;
          &lt;ns1:FirstElementID&gt;DDF00301&lt;/ns1:FirstElementID&gt;
          &lt;ns1:FirstEValue/&gt;
          &lt;ns1:SecondElement&gt;
              &lt;ns1:Else&gt;
                  &lt;ns1:Stuff&gt;yessir&lt;/ns1:Stuff&gt;
                  &lt;ns1:StuffVal&gt;0&lt;/ns1:StuffVal&gt;
              &lt;/ns1:Else&gt;
          &lt;/ns1:SecondElement&gt;        
      &lt;/ns1:FirstElement&gt;
      &lt;!-- ... times n the first element with a variating amount of children up to 15 levels deep --&gt;
  &lt;/ns0:root&gt;
  </code></pre>
  
  <p>I'd like this as a simple column output, ie:</p>
  
  <pre><code>FIRSTELEMENTID | FIRSTEVALUE      | SOMETHING | ELSE.STUFF | ELSE.STUFFVAL | ELSE.STUFFPARAM | RANDVALUE
  'AAF11303'     |'some random text'| 'Asdasd'  | 'sdf'      | 15            | NULL            | 'dosd'
  'AAF11303'     |'some random text'| 'Asdasd'  | 'jarjar'   | 16            | TRUE            | 'dosd'
  'DDF00301'     | NULL             | NULL      | 'yessir'   | 0             | NULL            | NULL
  </code></pre>
  
  <p>Now, the actual XML is a lot more complex and all the examples I've seen so far involve manually parsing the XML and basically passing each child element as its own XML and parsing that separately in the next loop of the script etc etc etc.</p>
  
  <p>Is there any way to just output the values flat out like that? Manually parsing several MB of XML with countless variations in which elements appear and how many times and where would make it a days long operation to do.</p>
  
  <p>I've been thinking about doing a CLR assembly to just do this with C# and pass the result into SQL as a table, but I'd like to know if there's another way all the same.</p>
  
  <p>Thanks!</p>