36

in MS Sql there are data types that are not supported by delphi 7, the xml datatype is one example.

I wish to convert the XML datatype to Text datatype, so that i could handle it in delphi.

Is there a way to convert from xml to text?

none
  • 4,669
  • 14
  • 62
  • 102

3 Answers3

73

A simple cast will suffice:

select cast(XMLCol as nvarchar(max)) as XMLCol

Or for non-unicode:

select cast(XMLCol as varchar(max)) as XMLCol

You can't convert explicitly to a 'text' data type.

I've added the as XMLCol to ensure that the converted data has the the same name as the column. You needn't have this, of course.

EDIT:

A few links. You are encouraged to use nvarchar(max) instead of text regardless. Microsoft have said they will be deprecating these types in future releases. nvarchar(max) ought to offer you 2GB:

http://www.petefreitag.com/item/734.cfm

http://www.teratrax.com/articles/varchar_max.html

http://msdn.microsoft.com/en-us/library/ms187752(v=SQL.90).aspx

James Wiseman
  • 29,946
  • 17
  • 95
  • 158
  • the problem with varchar is the limit of x bytes, which is not the case for xml. – none Jan 05 '11 at 15:53
  • 3
    Which version of SQL Are you using. nvarchar(max) should offer you up to 2GB. – James Wiseman Jan 05 '11 at 16:02
  • after a quick search , there is a difference between specifying varchar(5000) and varchar(max) – none Jan 05 '11 at 19:31
  • 1
    Neither CAST() or CONVERT() will properly unescape the xml. The handling of converting to a NULL when there's no matching tag on a nodeset query function also fails. – Jay Nov 03 '14 at 23:08
11
SELECT CAST(YourXMLColumn as nvarchar(max))
    FROM YourTable
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0

I just tried the follwing solution and yes, you do need the as XMLCol

select cast(XMLCol as nvarchar(max)) as XMLCol
Marcus Gallegos
  • 1,532
  • 1
  • 16
  • 31