如何从SQL中的ntext列中删除xml节点

I have a table with a column of type ntext. This column contains xml as string

我有一个表格,其中包含ntext类型的列。此列包含xml作为字符串

I want to delete one element that can exists more than once.

我想删除一个可以存在多个元素的元素。

How do I do that?

我怎么做?

Example xml input:

示例xml输入:

<CATALOG>
    <CD>
        <TITLE>Empire Burlesque</TITLE>
        <ARTIST>Bob Dylan</ARTIST>
        <COUNTRY>USA</COUNTRY>
        <COMPANY>Columbia</COMPANY>
        <PRICE>10.90</PRICE>
        <YEAR>1985</YEAR>
    </CD>
    <CD>
        <TITLE>Hide your heart</TITLE>
        <ARTIST>Bonnie Tyler</ARTIST>
        <COUNTRY>UK</COUNTRY>
        <COMPANY>CBS Records</COMPANY>
        <PRICE>9.90</PRICE>
        <YEAR>1988</YEAR>
    </CD>
</CATALOG>

I want to delete the node COUNTRY with a SQL update script

我想用SQL更新脚本删除节点COUNTRY

1 个解决方案

#1

If you need to do this, then you have to

如果你需要这样做,那么你必须这样做

load the data into a SQL variable of type XML

将数据加载到XML类型的SQL变量中

perform the modification on that variable

对该变量执行修改

update your table again

再次更新你的表

So you need to do something like this:

所以你需要做这样的事情:

DECLARE @XmlVar XML

SELECT @XmlVar = CAST(YourNtextColumn AS XML)
FROM dbo.YourTable
WHERE ID = 123

SET @XmlVar.modify('delete /CATALOG//CD/COUNTRY')

SELECT @XmlVar

Now in the end, you'd have to write back the modified XML variable to your table. The trouble is: you cannot convert from XML to NTEXT ...... so you should really fix your table structure first (make that column a XML in the first place!) before wasting your time trying to update this deprecated column type...

现在最后,您必须将修改后的XML变量写回到表中。麻烦的是:你无法从XML转换为NTEXT ......所以你应该首先修复你的表结构(首先使该列成为XML!)然后再浪费时间尝试更新这个不赞成的列类型。 ..

#1