CODEWITHSUNDEEP
sqlsql-server
Kimos
Kimos

Reputation: 99

How to delete xml node from ntext column in SQL

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

Upvotes: 1

Views: 437

Answers (1)

marc_s
marc_s

Reputation: 754478

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...

Upvotes: 1

Related Questions