sql - xmltype列与输出xml之间的两个表之间的SQL Server关系

我正在尝试编写查询，但尚未成功。如果需要，我需要一些帮助。

我有2个表，Category和Product。

分类表

列：Id int，ParentID int，XmlData XML

INSERT INTO Category
VALUES (1, 0,
'<category>
   <item>BMW</item>
   <item>MERCEDES</item>
   <item>AUDI</item>
   <item>WOLKSVAGEN</item>
</category>')

产品表：

列ID int，XmlData xml

INSERT INTO Product
VALUES (1, '<item>
    <category id="1" />
    <name>Carburetor</name>
    <model>MDS343498</model>
    <price>$3000</price>
    <stock>123</stock>
</item>')

我要合并相关的两个XML列。

我想要这样的输出：

'<item>
    <category id="1">
        <item>BMW</item>
        <item>MERCEDES</item>
        <item>AUDI</item>
        <item>WOLKSVAGEN</item>
    </category>
    <name>Carburetor</name>
    <model>MDS343498</model>
    <price>$3000</price>
    <stock>123</stock>
</item>'

最佳答案

用另一个节点替换一个节点有点棘手。以下代码将首先使用可更新的CTE查找合适的category，获取其XmlData，然后使用CTE对产品的UPDATE和XmlData新节点进行insert。

现在的问题是：类别存在两次：一次作为新节点，第二次作为旧节点。当我们插入as first时，我们可以确定第二次出现是要删除的一次：

DECLARE @Category Table(Id int, ParentID int, XmlData XML);
INSERT INTO @Category
VALUES (
1,
0,
'<category>
   <item>BMW</item>
   <item>MERCEDES</item>
   <item>AUDI</item>
   <item>WOLKSVAGEN</item>
</category>'
);

DECLARE @Product Table (Id int, XmlData xml);

INSERT INTO @Product
VALUES (
1,
'<item>
    <category id="1" />
    <name>Carburetor</name>
    <model>MDS343498</model>
    <price>$3000</price>
    <stock>123</stock>
</item>'
);

-可更新的CTE

WITH UpdateableCTE AS
(
    SELECT p.Id
          ,p.XmlData
          ,Cat.XmlData AS XmlToInsert
    FROM @Product AS p
    CROSS APPLY (SELECT c.XmlData FROM @Category AS c WHERE Id=p.XmlData.value('(/item/category/@id)[1]','int')) AS Cat
)
UPDATE UpdateableCTE
SET XmlData.modify(N'insert sql:column("XmlToInsert") as first into (/item)[1]');

-删除第二次出现

UPDATE @Product SET XmlData.modify(N'delete /item[1]/category[2]')

-XmlData中的结果：

SELECT * FROM @Product

<item>
  <category>
    <item>BMW</item>
    <item>MERCEDES</item>
    <item>AUDI</item>
    <item>WOLKSVAGEN</item>
  </category>
  <name>Carburetor</name>
  <model>MDS343498</model>
  <price>$3000</price>
  <stock>123</stock>
</item>

OT：您可能想将WOLKSVAGEN改成VOLKSWAGEN :-)

UPDATE保留属性<category id="1">，该属性在上面的方法中丢失了：

我使用FLWOR查询来预先组织XmlToInsert

WITH UpdateableCTE AS
(
    SELECT p.Id
          ,p.XmlData
          ,Cat.XmlData.query
          (
          N'<category id="{sql:column("CatId")}">
            {/category/item}
            </category>'
          ) AS XmlToInsert
    FROM @Product AS p
    CROSS APPLY (SELECT c.Id AS CatId, c.XmlData FROM @Category AS c WHERE Id=p.XmlData.value('(/item/category/@id)[1]','int')) AS Cat
)
UPDATE UpdateableCTE
SET XmlData.modify(N'insert sql:column("XmlToInsert") as first into (/item)[1]');

UPDATE @Product SET XmlData.modify(N'delete /item[1]/category[2]')

SELECT * FROM @Product

更新2：动态的差异方法

像这样尝试

WITH CTE AS
(
    SELECT p.Id
          ,p.XmlData
          ,Cat.XmlData.query
          (
          N'<category id="{sql:column("CatId")}">
            {/category/item}
            </category>'
          ) AS XmlToInsert
    FROM @Product AS p
    CROSS APPLY (SELECT TOP 1 c.Id AS CatId, c.XmlData FROM @Category AS c WHERE Id=p.XmlData.value('(/item/category/@id)[1]','int')) AS Cat
)
SELECT XmlToInsert AS [node()]
      ,XmlData.query('/item/*[local-name()!="category"]')
FROM CTE
FOR XML PATH('item')

关于sql - xmltype列与输出xml之间的两个表之间的SQL Server关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40633809/