title: Transforming XML in the XML data type in SQL Server
body: <p>I have a table called tags, which looks like this:</p>
  
  <pre><code> --------------------
  |TagId   |   TagName |
  --------------------
  | 1      |   Travel  |
   --------------------
  | 2      |   Gadgets |
   --------------------
  | 3      |   Hobbies |
   --------------------
  | 4      |   Movies  |
   -------------------- 
  </code></pre>
  
  <p>And I have another table, which has an XML data type column called Tags.</p>
  
  <pre><code>    -------------------------------------------------------------------------
     |PostId   |   Title          | Tags                                       |
      -------------------------------------------------------------------------
     | 1       |  Blog Post 1     | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;1&lt;/tag/&gt;&lt;tag&gt;2&lt;/tag&gt;&lt;/tags&gt;|
     --------------------------------------------------------------------------
     | 2       |  Blog Post 2     | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;2&lt;/tag/&gt;&lt;tag&gt;3&lt;/tag&gt;&lt;/tags&gt;|
     --------------------------------------------------------------------------
     | 3       |  Blog Post 3     | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;3&lt;/tag/&gt;&lt;tag&gt;4&lt;/tag&gt;&lt;/tags&gt;|
     --------------------------------------------------------------------------
  </code></pre>
  
  <p>I want to combine the data from these two tables, to create a single view, which looks like this. The number inside the  node should act as a foreign key, to the Tags table.</p>
  
  <pre><code>    ---------------------------------------------------------------------------
     | Title         | Tags                                                      |
      ---------------------------------------------------------------------------
     | Blog Post 1   | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;Travel&lt;/tag/&gt;&lt;tag&gt;Gadgets&lt;/tag&gt;&lt;tags&gt;     |
     ----------------------------------------------------------------------------
     | Blog Post 2   | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;Gadgets&lt;/tag/&gt;&lt;tag&gt;Hobbies&lt;/tag&gt;&lt;/tags&gt;   |
     ----------------------------------------------------------------------------
     | Blog Post 3   | &lt;xml&gt;&lt;tags&gt;&lt;tag&gt;Hobbies&lt;/tag/&gt;&lt;tag&gt;Movies&lt;/tag&gt;&lt;/tags&gt;    |
     ----------------------------------------------------------------------------
  </code></pre>
  
  <p>Is it possible to create a view like this? How do I do that?</p>