Transforming XML in the XML data type in SQL Server

[Paint]

I have a table called tags, which looks like this:\[code\] --------------------|TagId | TagName |--------------------| 1 | Travel | --------------------| 2 | Gadgets | --------------------| 3 | Hobbies | --------------------| 4 | Movies | -------------------- \[/code\]And I have another table, which has an XML data type column called Tags.\[code\] ------------------------------------------------------------------------- |PostId | Title | Tags | ------------------------------------------------------------------------- | 1 | Blog Post 1 | <xml><tags><tag>1</tag/><tag>2</tag></tags>| -------------------------------------------------------------------------- | 2 | Blog Post 2 | <xml><tags><tag>2</tag/><tag>3</tag></tags>| -------------------------------------------------------------------------- | 3 | Blog Post 3 | <xml><tags><tag>3</tag/><tag>4</tag></tags>| --------------------------------------------------------------------------\[/code\]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.\[code\] --------------------------------------------------------------------------- | Title | Tags | --------------------------------------------------------------------------- | Blog Post 1 | <xml><tags><tag>Travel</tag/><tag>Gadgets</tag><tags> | ---------------------------------------------------------------------------- | Blog Post 2 | <xml><tags><tag>Gadgets</tag/><tag>Hobbies</tag></tags> | ---------------------------------------------------------------------------- | Blog Post 3 | <xml><tags><tag>Hobbies</tag/><tag>Movies</tag></tags> | ----------------------------------------------------------------------------\[/code\]Is it possible to create a view like this? How do I do that?