时间戳

首页 > 解决方案 > 从表中的一个字段更新到 XML 代码的另一部分

sql-server - 从表中的一个字段更新到 XML 代码的另一部分

问题描述

我在 SQL 中创建了一个新的自定义列，它是作为 XML 字段的一部分创建的，

我需要从另一个名为“Comments”的字段中获取数据，并将其放入创建我的自定义列的 XML 代码中。我可以手动执行此操作，但是要更新 10 万行代码，我希望这可以实现。

先感谢您。

当前名为 Comments 的列只是一个纯文本字段，我需要获取此字段并更新部分 XML 字段，如下所示：

<CustomColumns>
    <CustomColumn>
       <Name>My New Column</Name>
       <DataType>0</DataType>
       <Value>The data to be updated</Value>
    </CustomColumn>
 </CustomColumns>

所以我在想我需要做一个 UPDATE 语句，从“评论”中提取当前数据，并将其插入到这个 XML 代码的部分中。

标签： sql-serverxmlsql-server-2014

解决方案

您可以使用 SQL Server 的 XML.modify('replace value of' ...) 来执行此操作。这是一个可以在 SSMS 中运行的示例，它应该可以让您朝着正确的方向前进。

-- SAMPLE DATA --
-- ADDED AN EXTRA CUSTOM COLUMN FOR THIS EXAMPLE --

DECLARE @Data TABLE ( id INT PRIMARY KEY IDENTITY (1,1), Comment VARCHAR(255), MyXmlCol XML );
INSERT INTO @Data ( Comment, MyXmlCol ) 
VALUES ( 
    'I need to update this comment to Value for "My New Column".', 
    '<CustomColumns>
      <CustomColumn>
        <Name>My New Column</Name>
        <DataType>0</DataType>
        <Value>The data to be updated</Value>
      </CustomColumn>
      <CustomColumn>
        <Name>My Other New Column</Name>
        <DataType>0</DataType>
        <Value>This value will not change.</Value>
      </CustomColumn>
    </CustomColumns>' 
);

SELECT id, Comment, MyXmlCol FROM @Data;

此初始 SELECT 返回：

+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id |                           Comment                           |                                                                                                                                    MyXmlCol                                                                                                                                    |
+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | I need to update this comment to Value for "My New Column". | <CustomColumns><CustomColumn><Name>My New Column</Name><DataType>0</DataType><Value>The data to be updated</Value></CustomColumn><CustomColumn><Name>My Other New Column</Name><DataType>0</DataType><Value>This value will not change.</Value></CustomColumn></CustomColumns> |
+----+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

当前的 XML 是：

<CustomColumns>
  <CustomColumn>
    <Name>My New Column</Name>
    <DataType>0</DataType>
    <Value>The data to be updated</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>My Other New Column</Name>
    <DataType>0</DataType>
    <Value>This value will not change.</Value>
  </CustomColumn>
</CustomColumns>

接下来，将“My New Column”的“Comment”更新为“Value”：

UPDATE @Data
SET
    MyXmlCol.modify( 'replace value of (/CustomColumns/CustomColumn[Name="My New Column"]/Value/text())[1] with (sql:column("Comment"))' )

SELECT id, Comment, MyXmlCol FROM @Data;

这个新的 SELECT 返回：

+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id |                           Comment                           |                                                                                                                                                      MyXmlCol                                                                                                                                                       |
+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | I need to update this comment to Value for "My New Column". | <CustomColumns><CustomColumn><Name>My New Column</Name><DataType>0</DataType><Value>I need to update this comment to Value for "My New Column".</Value></CustomColumn><CustomColumn><Name>My Other New Column</Name><DataType>0</DataType><Value>This value will not change.</Value></CustomColumn></CustomColumns> |
+----+-------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

这是更新的 XML：

<CustomColumns>
  <CustomColumn>
    <Name>My New Column</Name>
    <DataType>0</DataType>
    <Value>I need to update this comment to Value for "My New Column".</Value>
  </CustomColumn>
  <CustomColumn>
    <Name>My Other New Column</Name>
    <DataType>0</DataType>
    <Value>This value will not change.</Value>
  </CustomColumn>
</CustomColumns>

有几点需要注意：

replace value of (/CustomColumns/CustomColumn[Name="My New Column"]/Value/text())[1]
  • 使用“text()”设置节点的文本值。
  • [1] 是必需的，因为您只能使用“替换”定位 1 个节点。
  • 注意使用CustomColumn[Name="My New Column"]来限制更新。

另请注意：

Usingsql:column("Comment")允许您在不破坏 SQL 对 XML 的“字符串文字”要求的情况下引用表中的另一列（在本例中为 [Comment]）。

推荐阅读