title: Select all XML nodes from XML column
body: <p>I have a table with an XML column. The XML structure of each entry is completely flat, without even the parent tag - this is an example of one entry:</p>
  
  <pre><code>&lt;tag1&gt;1.22&lt;/tag1&gt;
  &lt;tag3&gt;5&lt;/tag3&gt;
  &lt;tag12&gt;-1.22&lt;/tag&gt;
  </code></pre>
  
  <p>So far, I've been able to do things like this:</p>
  
  <pre><code>SELECT CAST(xml_column AS NVARCHAR(MAX)) as XML_text
  </code></pre>
  
  <p>And parse the XML on my end. Or I apparently I can write <code>xpath</code> queries to select tags into columns, which is what I want, except there is like 1000 possible tag names, and I don't want to write them all out (and possibly miss one). </p>
  
  <p>In short, how do I go from this:</p>
  
  <pre><code>| ID | XML type column                |
  | 1  | &lt;tag1&gt;1&lt;/tag1&gt;&lt;tag2&gt;2&lt;/tag2&gt;   |
  | 2  | &lt;tag2&gt;8&lt;/tag2&gt;&lt;tag34&gt;1&lt;/tag34&gt; |
  </code></pre>
  
  <p>To this: </p>
  
  <pre><code>| ID | tag1 | tag2 | tag34 |
  | 1  | 1    | 2    | NULL  |
  | 2  | NULL | 8    | 1     |
  </code></pre>
  
  <p>for any tags I could find in my dataset, <strong>without knowing them in advance</strong>? I would settle for this as well:</p>
  
  <pre><code>| ID | tag   | value | 
  | 1  | tag1  | 1     |
  | 1  | tag2  | 2     |
  | 2  | tag2  | 8     |
  | 2  | tag34 | 1     |
  </code></pre>