时间戳

首页 > 解决方案 > 如何在 `ntext` 列上使用 `modify` 方法而不将列类型更改为 `xml`（使用 CAST 或 CONVERT 或其他方法）

sql-server - 如何在 `ntext` 列上使用 `modify` 方法而不将列类型更改为 `xml`（使用 CAST 或 CONVERT 或其他方法）

问题描述

我需要更新存储在ntext名为的列中的 XML 内容中的两个属性data

我尝试在列上使用或使用xml.modifyXQuery 方法，但没有成功：CASTCONVERTdata

尝试使用时CAST

UPDATE [dbo].[CodeSystemCodes_data] 
    SET
        (CAST([data] as xml)).modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@Description)[1] with sql:variable("@NEW_EXAM_NAME")')
    WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'

我收到这个错误

Incorrect syntax near '('

...在 CAST 之前，删除 '(' 也失败了

我最终创建了一个具有单一XML类型列的临时表，xmlData在该表上使用了 modify 方法，然后将数据返回到原始表，但这似乎有点过头了

如何在不使用任何中间表的情况下使用使用类型列的modify方法？也许与 CAST 或 CONVERT 或其他东西。我尝试使用和 XML 变量也没有成功。datantext

请记住，此时无法更改列类型，因为我不是 DBA 或具有决定该角色的人。

我使用 XML 修改方法而不是 REPLACE，因为我不希望沿 XML 数据进行错误替换。

还有其他类似的问题，但目前都没有答案

代码：

DECLARE @NEW_EXAM_NAME NVARCHAR(10) = 'BCC'
DECLARE @CODE_DESC NVARCHAR(50)

SET @CODE_DESC = @CURRENT_EXAM_CODE + ' - ' + @NEW_EXAM_NAME

CREATE TABLE #temp_xml (xmlData XML)

INSERT INTO #temp_xml
    SELECT CAST([data] AS XML)
    FROM [dbo].[CodeSystemCodes_data]   
    WHERE [data] LIKE '%' + @CURRENT_EXAM_CODE + '%'

UPDATE #temp_xml
SET xmlData.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@Description)[1] with sql:variable("@NEW_EXAM_NAME")')

UPDATE #temp_xml
SET xmlData.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@CodeAndDescription)[1] with sql:variable("@CODE_DESC")')
        
SELECT * FROM #temp_xml

UPDATE [dbo].[CodeSystemCodes_data] 
SET [data] = CAST(CAST((SELECT xmlData FROM #temp_xml) as nvarchar(max)) as ntext)
WHERE [data] LIKE '%' + @CURRENT_EXAM_CODE + '%'

DROP TABLE #temp_xml

标签： sql-serverxquery-sql

解决方案

不幸的是，当您将 XML 存储为文本时，您几乎被困在做类似的事情。

如果你想摆脱临时表的使用，你可以尝试这样的事情。您可以在 SSMS 中运行它。

/* Base table mock-up */
DECLARE @Data TABLE ( [data] NTEXT, [id] INT IDENTITY (1,1) );
INSERT INTO @Data ( [data] ) VALUES 
    ( '<root><values><val>Value 1</val><val>to_be_changed</val></values></root>' ),
    ( '<root><values><val>All is well here.</val><val>All is well here, too.</val></values></root>' ),
    ( '<root><values><val>Another value.</val><val>to_be_changed</val></values></root>' );

/* Find/Replace variables */
DECLARE 
    @find_value VARCHAR(50) = 'to_be_changed',
    @replace_value VARCHAR(50) = 'Value 2';

/* Create a table variable to temporarily house the ntext data as xml so the XML may be modified */
DECLARE @Temp TABLE ( DataXml XML, id INT );

/* Insert [data] into the XML column */
INSERT INTO @Temp ( DataXml, [id] )
SELECT CAST ( [data] AS XML ), [id] FROM @Data WHERE [data] LIKE '%' + @find_value + '%';

/* Show the @Data resultset before modifying */
SELECT * FROM @Data;

/* The WHILE is to make sure every node that requires updating gets updated */
/* Modify each instance matching the @find_value criteria */
WHILE EXISTS ( SELECT * FROM @Temp WHERE DataXml.exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
UPDATE @Temp
SET
    DataXml.modify ('
        replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
        with sql:variable("@replace_value")
    ');

/* Update the results back to the ntext column */
UPDATE @Data
SET
    [data] = CAST ( t.DataXml AS NVARCHAR(MAX) )
FROM @Data d
INNER JOIN @Temp t
    ON d.id = t.id;

/* Show the updated @Data resultset */
SELECT * FROM @Data;

@Data 的初始选择：

/* Show the @Data resultset before modifying */
SELECT * FROM @Data;

退货

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>to_be_changed</val></values></root>                    |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>to_be_changed</val></values></root>             |  3 |
+---------------------------------------------------------------------------------------------+----+

@Data 的最终结果集：

/* Show the updated @Data resultset */
SELECT * FROM @Data;

退货

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val></values></root>                          |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root>                   |  3 |
+---------------------------------------------------------------------------------------------+----+

可能的替代方法： 也许REPLACE对您的文字简单。

UPDATE @Data
SET
    [data] = REPLACE ( CAST ( [data] AS NVARCHAR(MAX) ), @find_value, @replace_value )
FROM @Data d
WHERE
    d.[data] LIKE '%' + @find_value + '%';

更新：

我应该更清楚地说“我不想使用任何类型的中间表”

/* For-each find/replace instance found... */
WHILE EXISTS ( SELECT * FROM @Data WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
BEGIN

    DECLARE @id INT, @xml XML;
    SELECT TOP 1
        @id = id,
        @xml = CAST ( [data] AS XML )
    FROM @Data
    WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1;

    -- Modify the XML --
    SET @xml.modify('
        replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
        with sql:variable("@replace_value")
    ');

    -- Update the modified XML --
    UPDATE @Data
    SET
        [data] = CAST ( @xml AS NVARCHAR(MAX) )
    WHERE id = @id;

END

/* Show the updated resultset */
SELECT * FROM @Data ORDER BY id;

更新的结果集：

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val><val>Value 2</val></values></root>        |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root>                   |  3 |
+---------------------------------------------------------------------------------------------+----+

由 OP 更新

感谢您的最后一个解决方案，我最终这样做了，不需要while循环

DECLARE @CURRENT_EXAM_CODE NVARCHAR(10) = 'BXC_14B'
DECLARE @NEW_EXAM_NAME NVARCHAR(10) = 'BCC'
DECLARE @CODE_DESC NVARCHAR(50)
DECLARE @XML_DATA XML


-- convert existing NTEXT data into XML
SELECT @XML_DATA =
    CAST([data] as xml)
    FROM [dbo].[CodeSystemCodes_data]   
    WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'

-- update the xml data
SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@Description)[1] with sql:variable("@NEW_EXAM_NAME")')

SET @CODE_DESC = @CURRENT_EXAM_CODE + ' - ' + @NEW_EXAM_NAME

SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@CodeAndDescription)[1] with sql:variable("@CODE_DESC")')

-- convert xml data back to ntext type
UPDATE [dbo].[CodeSystemCodes_data] 
    SET 
        [data] = CAST(CAST(@XML_DATA as nvarchar(max)) as ntext)
    WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'

推荐阅读