如何合并 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>

我最终想要实现的是一个视图，显示按照 FKSiteID 值遵循相同架构的单个 xml 文档中分组的所有关键字。

作为中间步骤，我试图提取所有关键字，但如果不使用表函数并对表进行交叉应用，我就无法做到这一点。

还有其他提示吗？

Answer 1

合并文档是可行的，但是您将受到 xquery 中的命名空间错误/功能的影响。似乎命名空间与 FOR XML 一起使用时会级联到所有节点级别。它确实创建了有效的 XML，但其可读性较差且完全冗余。更多此处< /a>

希望这就是您正在寻找的：

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;

Answer 2

通过更好地谷歌搜索，我在 msdn 上找到了指导如何解决问题的指南。

这是我试图按 ST.FKSiteID 分组并聚合值的问题的部分解决方案

WITH XMLNAMESPACES ('http://www.educations.com/Info/Keywords' AS a )
(
    SELECT ST.FKSiteID AS SiteID, K.Keywords.value('.','nvarchar(max)') AS Keyword
    FROM 
        info.SearchTexts ST
        CROSS APPLY ST.Keywords.nodes('a:keywords/a:keyword') AS K(Keywords)
)

，但显然没有聚合函数可处理 xml 数据。

太可惜了。