locked
encoding issue RRS feed

  • Question

  • Hi all,

    Please let me know why i am getting "unable to swith encoding error" for below statement

    select cast(N'<?xml version="1.0" encoding="utf-8"?> <a>sss</a>' as xml)

    is there any workaround for this other than making it utf-16 or replace <?xml version="1.0" encoding="utf-8"?> with ''.Im not in a position to ask my client to do this.

    if not possible please share the technical details

    Thanks,

    Shipin

    Wednesday, May 30, 2012 7:27 PM

Answers

  • Just removing the N worked for me:

    select cast('<?xml version="1.0" encoding="utf-8"?> <a>sss</a>' as xml)

    • Proposed as answer by Edward Zhu Thursday, June 7, 2012 1:32 AM
    • Marked as answer by Iric Wen Thursday, June 7, 2012 1:40 AM
    Thursday, May 31, 2012 11:11 AM
    Answerer

All replies

  • Cause SQL Server stores any XML internally as UTF-16. So that you can seamlessly work with different XML data. You normally need to change the encoding only when you export it to different systems. You can, e.g. specify an encoding when using BCP, but not to UTF-8. The simplest solution can be your own utility using C#:

    private XmlDocument ChangeXmlEncoding(XmlDocument xmlDoc, string newEncoding)
    {
        if (xmlDoc.FirstChild.NodeType == XmlNodeType.XmlDeclaration)
        {
            XmlDeclaration xmlDeclaration = (XmlDeclaration)xmlDoc.FirstChild;
            xmlDeclaration.Encoding = newEncoding;
        }
        return xmlDoc;
    }

    Thursday, May 31, 2012 9:34 AM
  • Just removing the N worked for me:

    select cast('<?xml version="1.0" encoding="utf-8"?> <a>sss</a>' as xml)

    • Proposed as answer by Edward Zhu Thursday, June 7, 2012 1:32 AM
    • Marked as answer by Iric Wen Thursday, June 7, 2012 1:40 AM
    Thursday, May 31, 2012 11:11 AM
    Answerer
  • But then you will no longer be able to use correctly encoded Unicode characters..
    Thursday, May 31, 2012 11:32 AM