在SQL Server中转换XML数据类型

I have a table called tags, which looks like this:

我有一个名为tags的表格，它是这样的:

 --------------------
|TagId   |   TagName |
--------------------
| 1      |   Travel  |
 --------------------
| 2      |   Gadgets |
 --------------------
| 3      |   Hobbies |
 --------------------
| 4      |   Movies  |
 --------------------

And I have another table, which has an XML data type column called Tags.

我还有另一个表，它有一个XML数据类型列，名为Tags。

    -------------------------------------------------------------------------
   |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>|
   --------------------------------------------------------------------------

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.

我想把这两个表的数据结合起来，创建一个这样的视图。节点内的数字应该作为一个外键，指向标记表。

    ---------------------------------------------------------------------------
   | 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>    |
   ----------------------------------------------------------------------------

Is it possible to create a view like this? How do I do that?

有可能创建这样的视图吗?我该怎么做呢?

2 个解决方案

#1

Something like this should do the trick:

像这样的东西应该可以达到这个目的:

/* SQL Follows */
select
postID, title,
tags,
(
    select 
        /* 4. Retrieve the tag name */
        y.tag.value('.', 'int') tagID,
        t.tagName
    from 
        /* 2. Shred the XML into nodes */
        p.tags.nodes('/xml/tags/tag') as y(tag)
        /* 3. Join the tag ID onto the tags table. */
        inner join #tags t on t.tagID = y.tag.value('.', 'int')
    for 
        /* 5. Convert it into XML */
        xml path('tag'), type
)tags2
/* 1. For each post */
from #posts p

I've used temp tables #tags and #posts in this example. To get the exact output you are after you will need to tweak the XML a little.

我在这个示例中使用了临时表#标记和#posts。要获得准确的输出，您需要稍微调整一下XML。

#2

Well I won’t provide a full solution because I don’t know how is your data. I’m sure it isn’t as simple as you displayed, but I think I can point you in the right direction.

我不会提供完整的解决方案因为我不知道你们的数据如何。我肯定它不像你展示的那么简单，但我想我可以给你指明正确的方向。

I would do a replace on your full string, looking for ID (don’t look just for 1, because you can have 11 for example and that would fail) and then replace it by a select where tagID=ID

我将对整个字符串执行替换，查找ID(不要只查找1，因为您可能有11个，那样会失败)，然后用select where tagID=ID替换它

select replace('<xml><tags><tag>1</tag/><tag>2</tag></tags>','<tag>1</tag/>',(select name from tags where tagid=1))

result:

结果:

<xml><tags>Travel<tag>2</tag></tags>

of course, you would need to do this to each tag, than it is up to you how to do it. You could loo the tag table or if you have just a few tags, run the replace a few times

当然，您需要对每个标记执行此操作，而不是由您决定如何执行。您可以在标记表上使用，或者如果您只有几个标记，那么可以运行几次替换。

#1