Search code examples
sql-serverxmldelphitextdelphi-7

ms sql xml data type convert to text

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?

Solution

  • 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