How to delete xml node from ntext column in SQL

Question

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

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

How do I do that?

Example xml input:

<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

ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. See details here. And if your column really contains XML - you should really use the XML datatype in SQL Server 2005 and newer — marc_s
– marc_s, Commented Jul 3, 2015 at 14:09
If your column really is ntext (which should never be) its contents is just a chunk of text. There is no way other than parse it and remove the nodes on your own. The best solution is to change it to xml column and use specialized methods, but if that's not possible, I suppose the next best alternative is to do it client-side, using dedicated xml parsing functions. — Alejandro
– Alejandro, Commented Jul 3, 2015 at 14:14
unfortunately is it ntext. its an old database and i cannot change the colum to a different type. — Kimos
– Kimos, Commented Jul 3, 2015 at 14:19

marc_s · Accepted Answer · 2015-07-03 14:18:08Z

1

If you need to do this, then you have to

load the data into a SQL variable of type XML
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...

answered Jul 3, 2015 at 14:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kimos Over a year ago

Thanks for the answers. I have made an SQL command to delete the node i want.

Collectives™ on Stack Overflow

How to delete xml node from ntext column in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related