代码之家 › 专栏 › 技术社区 › Denis

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

sql-server-2012 xml sql-server sql

Denis · 技术社区 · 6 年前

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

tblData 它包含一个具有多个属性的xml字符串 id 身份证件 使用在中找到的新ID创建 tblMap . 如果在中找不到id映射 tblMap 那么它应该保持原样。有没有关于我如何做到这一点的提示?

我尝试的是:

我想用武力强迫别人做点什么 XMLText.modify('replace value of ...') This StackOverflow Article

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

3 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

我的建议是 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;

一些解释

为了使用中的映射和数据 <attributes> 元素与 <maps> 元素。

这个 .query() 将运行属性并搜索 < 对于管件重新映射。奇迹发生在 (val1,val2)[1] . 这就像 COALESCE() . 它将选择第一个非空 -值,它是管件新id或现有值。

.modify() [Value]

Denis 6 年前

/attributes

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>' );

SELECT * FROM @tblMap;
SELECT * FROM @tblData;

-- Update table with new XML
with cte as (
select d.*, (
    select isnull(m.newid, a.c.value('./@id', 'int')) as [@id], a.c.value('./@value', 'nvarchar(max)') as [@value]
    from d.Value.nodes('/attributes[1]/attribute') a(c)
        left join @tblmap m on m.oldid = a.c.value('./@id', 'int')
    for xml path('attribute'), type, root('attributes')
    ) as [NewValue]
from @tblData d
)
update c set Value = NewValue
from cte c;

-- New version
select * from @tblData;

(我已经将您的表转换为表变量,因为它在实例上没有留下任何足迹。其他一切都是一样的。)

不幸的是,如果您的实际XML模式比您的示例所示的更复杂,并且在XML模式下包含额外的不可预测的元素和/或属性,那么这种方法可能变得难以实现节点。在这种情况下,我建议要么使用FLWOR(至少对我来说,这是一种速度慢且很难编写的更新),要么使用游标更新。

要调试:

-- Update table with new XML
with cte as (
select d.*, (
    select isnull(m.newid, a.c.value('./@id', 'int')) as [@id], a.c.value('./@value', 'nvarchar(max)') as [@value]
    from d.Value.nodes('/attributes[1]/attribute') a(c)
        left join @tblmap m on m.oldid = a.c.value('./@id', 'int')
    for xml path('attribute'), type, root('attributes')
    ) as [NewValue]
from @tblData d
)
SELECT c.SourceID,
   c.SourceRecID,
   c.Value,
   c.NewValue
from cte c;

Thom A 6 年前

ORDER BY (SELECT NULL) 然而,在这里,我没有太多的选择,除了希望顺序是节点的顺序。

无论如何,解决方案涉及动态SQL;也许有一种“更好”的方法可以做到这一点,但如果有,我不知道。我建议先做一些像样的测试,但是,这似乎会得到您想要的结果:

DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT NCHAR(10) +
                         N'UPDATE tblData' + NCHAR(10) + 
                         N'SET [Value].modify(''replace value of (/attributes/attribute/@id)[' + CONVERT(varchar(4),ROW_NUMBER() OVER (PARTITION BY D.SourceID, D.SourceRecID ORDER BY (SELECT NULL))) + N'] with "' + CONVERT(varchar(4),ISNULL(M.newid,V.AA.value('@id','int'))) + N'"'')' + NCHAR(10) +
                         N'WHERE SourceID = ' + CONVERT(varchar(4),D.SourceID) + NCHAR(10) +
                         N'  AND SourceRecID = ' + CONVERT(varchar(4),D.SourceRecID) + N';'
                  FROM tblData D
                       CROSS APPLY D.[Value].nodes('attributes/attribute') V(AA)
                       LEFT JOIN tblmap M ON V.AA.value('@id','int') = M.oldid
                  FOR XML PATH(N'')),1,1,N'');

EXEC sp_executesql @SQL;