时间戳

首页 > 解决方案 > 基于映射表替换 XML 列中的多个值

sql - 基于映射表替换 XML 列中的多个值

问题描述

假设我有一个名为的映射表tblMap，它只是将旧属性 ID 映射到新属性 ID（oldID -> newID）。值得注意的是：newID 不包含在 oldID 的列表中。

然后我有一个表tblData，其中包含一个具有许多属性id的 xml 字符串。我想id用在tblMap. 如果没有找到 id 映射，tblMap那么它应该保持原样。关于如何实现这一目标的任何提示？

我尝试了什么：

我试图强制使用XMLText.modify('replace value of ...')如下所述的内容：这篇 StackOverflow 文章，但未能成功使其正常工作。

CREATE TABLE tblmap (
  oldid INT, 
  newid INT
)
GO

INSERT INTO tblMap
VALUES
( 58, 1002),
( 85, 5002),
( 70, 3202),
(2, 2340),
(5, 7432)
GO

CREATE TABLE tblData ( [SourceID] int, [SourceRecID] bigint, [Value] xml )
GO

INSERT INTO tblData
VALUES
( 1, 0, N'<attributes><attribute id="58" value="0" /><attribute id="86" value="1" /><attribute id="85" value="1" /><attribute id="70" value="0" /><attribute id="38" value="0" /><attribute id="68" value="0" /><attribute id="42" value="1" /><attribute id="67" value="1" /><attribute id="62" value="1" /></attributes>' ), 
( 1, 686, N'<attributes><attribute id="1" value="0.25" /><attribute id="4" value="1" /><attribute id="10" value="3" /><attribute id="11" value="1" /><attribute id="12" value="6" /></attributes>' ), 
( 1, 687, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ), 
( 1, 688, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' )


SELECT *
FROM tblMap
GO

SELECT *
FROM tblData
GO

为了您的方便，我在这里构建了所有模式/示例数据： https ://rextester.com/MUMI61854

标签： sqlsql-serverxmlsql-server-2012

解决方案

我的建议呼吁XQuery救援（txh Roger Wolf 声明的表变量，也使用了它们......）：

declare @tblmap table (oldid INT, newid INT);

INSERT INTO @tblMap
VALUES
( 58, 1002),
( 85, 5002),
( 70, 3202),
(2, 2340),
(5, 7432);

declare @tblData table ([SourceID] int, [SourceRecID] bigint, [Value] xml);

INSERT INTO @tblData
VALUES
( 1, 0, N'<attributes><attribute id="58" value="0" /><attribute id="86" value="1" /><attribute id="85" value="1" /><attribute id="70" value="0" /><attribute id="38" value="0" /><attribute id="68" value="0" /><attribute id="42" value="1" /><attribute id="67" value="1" /><attribute id="62" value="1" /></attributes>' ), 
( 1, 686, N'<attributes><attribute id="1" value="0.25" /><attribute id="4" value="1" /><attribute id="10" value="3" /><attribute id="11" value="1" /><attribute id="12" value="6" /></attributes>' ), 
( 1, 687, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ), 
( 1, 688, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' );

--查询将一次性完成整个过程

WITH CombineThem AS
(
    SELECT d.SourceID
          ,d.SourceRecID
          ,d.[Value]
          ,(SELECT
               (SELECT * 
                FROM @tblMap 
                FOR XML PATH('map'),ROOT('maps'),TYPE)
              ,[Value] AS [*]
             FOR XML PATH('Combined'),TYPE) AS Combined
    FROM @tblData d
)
,updateableCTE AS
(
    SELECT ct.[Value]
          ,ct.Combined
           .query('<attributes>
                   {
                    for $attr in /Combined/attributes/attribute
                    return <attribute id="{
                                           (
                                            /Combined/maps/map[oldid[1]=$attr/@id]/newid
                                            ,$attr/@id
                                           )[1]
                                          }" 
                                      value="{$attr/@value}"/> 
                   }  
                   </attributes>') NewValue
    FROM CombineThem ct
)
UPDATE updateableCTE SET [Value]=NewValue;

--检查结果

SELECT * FROM @tblData;

一些解释

为了使用映射和数据，XQuery我在第一个 CTE 中创建了一个组合 XML。这将包括整个<attributes>元素和一个<maps>元素。

将.query()遍历属性并搜索<maps>合适的重新映射。魔术发生在(val1,val2)[1]. 这表现得像COALESCE(). 它将选择第一个非 null值，即合适的新 id 或现有值。

无需使用.modify()最后一步更新 XML，而是[Value]一次性将列设置为新创建的 XML。

推荐阅读