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?
Add a comment |
3 Answers
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
4 Comments
none
the problem with varchar is the limit of x bytes, which is not the case for xml.
Mutation Person
Which version of SQL Are you using. nvarchar(max) should offer you up to 2GB.
none
after a quick search , there is a difference between specifying varchar(5000) and varchar(max)
Jay
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.
SELECT CAST(YourXMLColumn as nvarchar(max)) FROM YourTable 
1 Comment
Squazz
This is what I normally do, it's so clean, easy to remember, hard to mess up :)
I just tried the follwing solution and yes, you do need the as XMLCol
select cast(XMLCol as nvarchar(max)) as XMLCol 