• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5840
  • Last Modified:

converting xml to varchar

Is it possible to convert xml to varchar using cast command?

I am getting the error in multiple collations:
"Conversion of one or more characters from XML to target collation impossible"

Thanks
0
anushahanna
Asked:
anushahanna
  • 4
  • 3
1 Solution
 
pcelbaCommented:
CAST is possible and the error message is correct most probably.

Try CAST with explicit collation:

SELECT CAST(YourXMLcolumn collate YourCollate AS varchar(nnn)) FROM YourTable

YourCollate should be of such value which allows all XML characters conversion.

You may start with collation same as you have set for XML column and then you may try to change it.
0
 
anushahannaAuthor Commented:
I tried syntax: SELECT CAST(YourXMLcolumn collate SQL_Latin1_General_CP850_BIN AS varchar(max)) FROM YourTable
and the error is
"Expression type xml is invalid for COLLATE clause."
0
 
pcelbaCommented:
Sorry. I've tried it on nvarchar column...

But it means if you use nvarchar it will work for you without collation:
SELECT CAST(YourXMLcolumn AS nvarchar(max)) FROM YourTable

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
anushahannaAuthor Commented:
sure enough.

what is the technical reason that varchar gets the error?
0
 
pcelbaCommented:
The collation of the varchar column does not recognize some characters contained in XML. And to change some characters in XML is not allowed because it could change its meaning in unpredictable way.

If you use nvarchar then no conversion is needed because all characters are encoded in doublebyte Unicode.
0
 
anushahannaAuthor Commented:
Thanks a lot, pcelba
0
 
pcelbaCommented:
You are welcome!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now