0

I have a table include XML column with ntext data type.

CREATE TABLE #Testing 
(
    Id int identity,
    content ntext
)

INSERT INTO #Testing
VALUES (N'<?xml version="1.0" encoding="UTF-8"?>
<Data <BankAcc><Bankname value="TEST Qərib Bank "/><AccNum value="TEST1221"/></BankAcc>
</Data>')

I want to insert this data <Owner value="Qərib"/> into existing ntext data type xml column with code below

 update #Testing
 set content.modify(N'insert <Owner value="Qərib"/> into (/Data)[1]')

but I get an error:

Msg 258, Level 15, State 1, Line 12
Cannot call methods on ntext

So I try to use cast

update #Testing
 set cast(content as varchar(max)).modify(N'insert <Owner value="Qərib"/> into (/Data)[1]')

then I got this error message:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.

Any solution ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
garib hasanov
  • 1
  • 3
  • 2
    Why are you *still* using `ntext`? It's been deprecated for *at least* 15 years. It's long past time you start using `nvarchar(MAX)`. – Thom A Sep 25 '20 at 13:40
  • 1
    Also, `ntext` isn't `xml`. if you want to use `xml` functions on a value it needs to be the `xml` data type. Don't store your XML data in a 15 year deprecated data type and use the appropriate `xml` data type. – Thom A Sep 25 '20 at 13:41
  • @Larnu i know and understand you.but structure made with ntext data type.Its legacy.i dont need to change any structure.the xml data located in ntext data type column.. – garib hasanov Sep 25 '20 at 13:52
  • 1
    Even legacy applications should have been updated by now. Like I said, `ntext` has been deprecated for *at least* 15 years. – Thom A Sep 25 '20 at 14:03

1 Answers1

0

convert your data type to nvarchar(max), varchar(max) or varbinary(max)

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

Reference

If it is proper xml then XML data type will fit best

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72