XML string with stored procedure

• Chris Lee-152270

  SSCommitted

  Points: 1809

  More actions

  December 13, 2005 at 10:43 am

  #169860

  Hi,

  I have SQL 2005 and defined a local variable @sql varchar(max) to hold a XML string in a stored procedure. The XML format looks like:

  <

  SysObjects>

  <

  TableName TblNme="TableName1">

  <

  syscolumn ColNme="ColName1"/>

  <

  syscolumn ColNme="ColName2"/>

  <

  syscolumn ColNme="ColName3"/>

  </

  TableName>

  <

  TableName TblNme="TableName2">

  <

  syscolumn ColNme="ColName1"/>

  <

  syscolumn ColNme="ColName2"/>

  </

  TableName>

  </SysObjects>

  Somehow, I am getting:

  The XML parse error 0xc00ce560 occurred on line number 131, near the XML text " <syscolumn ColNme="IsActi".

  Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1

  The error description is 'A string literal was not closed.'.

  Msg , State 3, Procedure sp_xml_removedocument, Line 1

  sp_xml_removedoc8179, Level 16, State 5, Procedure spTest, Line 64

  Could not find prepared statement with handle 0.

  The statement has been terminated.

  Msg 6607, Level 16ument: The value supplied for parameter number 1 is invalid.

  I did not fins any non displayable character embaded in the string. It works when I cut, paste and run the stored procedure with individual table. Somehow it stops working when I try to some of tables together. Any hint? Thanks.

  Chris

   

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  December 16, 2005 at 1:16 am

  #609978

  Well, the error message is pretty much telling you where to look. The column IsActi*** (*** means whatever should really come here) seems so have a double quote character in it.

  Anyway, if you are going to use XML, why not use the xml datatype instead of a varchar(max) and sp_prepare_xml etc?

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/