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

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

  •  1
  • 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 年前
        1
  •  2
  •   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]

        2
  •  2
  •   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;
    
        3
  •  1
  •   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;