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

是否从SQL中XML类型列的特定行中删除元素?

  •  0
  • Denis  · 技术社区  · 6 年前

    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>' )
    GO
    
    SELECT *
    FROM tblData
    

    假设我想删除 attribute id =7,从 Value 列在哪里 SourceId SourceRecID = 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="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ), 
    

    注意 <attribute id="7" value="1" /> 现在该行的原始xml中缺少。

    我想不出一个办法。。。

    1 回复  |  直到 6 年前
        1
  •  1
  •   Panagiotis Kanavos    6 年前

    您可以尝试:

    update tblData 
    set 
        Value.modify('delete (/attributes/attribute[@id="7"])')
    where 
        SourceRecID = 687 and 
        SourceID = 1
    

    SQL Server的XQuery包含一个扩展, XML DML 这允许修改XML值。中显示了几个示例 Examples of using XQuery to update XML Data in SQL Server .

    modify 函数用于执行XMLDML查询。在该查询中 delete 关键字可用于删除与查询匹配的所有元素

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

    SourceRecID 687所在行的内容包括:

    <attributes>
      ...
      <attribute id="6" value="0" />
      <attribute id="7" value="1" />
      <attribute id="9" value="1" />
      ...
    </attributes>
    

    update tblData 
    set Value.modify('delete (/attributes/attribute[@id="7"])')
    where [SourceRecID]=687 and SourceID = 1
    

    内容包括:

    ...
    <attribute id="5" value="252.00" />
    <attribute id="6" value="0" />
    <attribute id="9" value="1" />
    ...