Utf-16

[Timothee Cazares]

Theabove is all just my opinion on what you should do.

As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!

I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Not a problem. I imagine there are other solutions as well. If you KNOW you are wanting NVARCHAR for sure, you could also do a REPLACE on the utf-8 to be utf-16. This is MAY break the XML though (shouldn't but never say never). Or if you are ALWAYS going to be working with utf-8 XML, then having the first parameter as a VARCHAR would save some conversions.

You can see there are a lot of encoding types. My approach above ONLY handles utf-8 and casts that to VARCHAR first. I am not certain if any of the other types (such as US-ASCII) would need to be converted to VARCHAR or if NVARCHAR can handle it. I expect if you don't know what encoding(s) will be used, you will need to do trial and error, with my above method, on all different encoding types and there are a lot.

The above approach ends up using 2 try catch blocks which I generally try to avoid in SQL if I can, but I think this is going to be easier than trying to test and capture all possible encoding types. Plus it handles the case where it can't convert to VARCHAR or NVARCHAR. I left the explicit NVARCHAR conversion in even though we have the NVARCHAR at the start because to me it makes it clearer to see what we are doing. That is, it increases readability for future developers. It is not required.

Plus, that last CONVERT for handling the error could be handled by parsing the XML rather than converting it. Parsing will likely give better performance, but I expect it to be very minimal performance gain. In this case, the XML is short, so converting it and doing a LIKE comparison on it should be quick and allows you to handle the error in the event some encoding type can't be converted to VARCHAR or NVARCHAR.

3a8082e126