xml - 如何从 SQL Server 中的 Xml 字段合并多个项目

Question

我正在尝试使用我们数据库中的一些 xml 数据。

起点是一个具有以下粗略轮廓的表格:

CREATE TABLE MyTable
(
    ID INT NOT NULL IDENTITY(1,1),
    ...,
    FKSiteID INT NOT NULL REFERENCES ...,
    ...,
    Keywords XML(DOCUMENT info.Keywords) NULL
)

典型的 xml 片段可能是:

<keywords xmlns="http://www.educations.com/Info/Keywords">
  <keyword>keyword 1</keyword>
  <keyword>keyword 2</keyword>
  <keyword>keyword 3</keyword>
  <keyword>keyword 4</keyword>
  <keyword>keyword 5</keyword>
</keywords>

最后我想要实现的是一个 View ，显示按照 FKSiteID 的值在相同模式下分组在单个 xml 文档中的所有关键字。

作为中间步骤，我试图提取所有关键字，但如果不使用表函数和 CROSS APPLY 到表中，我就无法做到这一点。

还有什么提示吗？

Answer 1

合并文档是可行的，但是您将受到 xquery 中命名空间错误/功能的影响。似乎命名空间与 FOR XML 一起使用时会在所有节点级别上级联。它确实创建了有效的 XML，但它不那么可读且完全多余。更多 here

希望这就是你要找的:

declare @MyTable table (
    ID INT NOT NULL IDENTITY(1,1),
    FKSiteID INT NOT NULL,
    Keywords XML NULL
)

insert into @MyTable (FKSiteID, Keywords)
    values  (1, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 1</keyword>
                  <keyword>keyword 2</keyword>
                  <keyword>keyword 3</keyword>
                  <keyword>keyword 4</keyword>
                  <keyword>keyword 5</keyword>
                </keywords>'
            ),
            (1, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 6</keyword>
                  <keyword>keyword 7</keyword>
                </keywords>'),
            (2, '<keywords xmlns="http://www.educations.com/Info/Keywords">
                  <keyword>keyword 21</keyword>
                </keywords>')

-- you probably have lookup table instead of the below cte          
;with XMLNAMESPACES('http://www.educations.com/Info/Keywords' AS ns),
c_Sites (FKSiteId)
as  (   select  distinct FKSiteId 
        from    @MyTable
    )
select  FKSiteID, 
        (   select Keywords.query('ns:keywords/*')
            from    @MyTable i 
            where   i.FKSiteID = O.FKSiteId
            for xml path(''), root('keywords'), type
        )
from c_Sites o;