OPENXML and ntext problem

• Mark Macumber

  Mr or Mrs. 500

  Points: 585

  More actions

  October 13, 2005 at 10:53 pm

  #95441

  Hey all, I have a problem with openxml. In my ASP.NET application I use XmlSerializer to turn a object into XML, things work fine. I then send the xml to a stored procedure on the SQL server (2000). The weird thing is the same XML can be sent to two different SPs and one does not work when declaring one of the XML fields as ntext.

  ie. in one I do an insert,

  insert into table1 (cust_id, job_requested, job_status)

  select @useCustId,

  xmlDetails.Description,

  xmlDetails.StatusId,

  from OPENXML(@idoc, 'JobList/Job', 2)

  WITH (CustomerId int, Description ntext, StatusId int) xmlDetails

  Now this is trimed down, but it works fine. now when the same XML gets sent to another SP that updates like:

  update job_main set

  job_requested = xmlDetails.Description,

  job_status = xmlDetails.StatusId,

  from OPENXML(@idoc, 'JobList/Job', 2)

  with (Id int, Description ntext, StatusId int) xmlDetails

  where job_id = xmlDetails.Id

  Now this one fails with 'Internal SQL Server error.' And I have no idea why, as it is the same XML as in the insert statement. If i change the ntext to nvarchar or varchar it works, but text and ntext do not.

  Anyone seen anything like this, or know if i'm doing something stupid? I have no idea.

  Thanks all

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  October 14, 2005 at 2:12 am

  #597285

  Try these links for code with NText and Text.  Hope this helps.

  http://www.sommarskog.se/arrays-in-sql.html#OPENXML

  http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp

  Kind regards,

  Gift Peddie

  Kind regards,
  Gift Peddie