Post reply

OPENROWSET XML from an Execution Plan causes encoding error

  • June 12, 2008 at 9:41 am

    #187875

    Hello all,

    I can't believe this isn't working. I've got incredibly simple code. But I'm missing something. I'm saving an execution plan to a file with an XML extension. Then I'm running this code:

    DECLARE @xml xml

    SELECT @xml = (SELECT *

    FROM OPENROWSET (BULK 'C:\mypath\xmltest.xml', SINGLE_BLOB) XMLShowPlan )

    And getting this error:

    Msg 9402, Level 16, State 1, Line 3

    XML parsing: line 1, character 39, unable to switch the encoding

    I've tried converts, moving it into a varchar(max) field (which works, it's there, but then converting the VARCHAR to XML fails with the same error)... I'm stuck here. Has anyone done this before? Surely they have.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 12, 2008 at 9:53 am

    #827966

    The file as saved from Management Studio seems to be ANSI even though the XML indicates it is UTF-16.

    One possible solution, open the file in notepad and save as Unicode instead of ANSI.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 12, 2008 at 10:26 am

    #827980

    Yes, that was the answer.

    Well, at least it wasn't something stupid that I did (for once).

    This is a bit of a pain. I wonder if there's a work around.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 5, 2014 at 12:21 pm

    #1694458

    Ran into this again, 6 years later. Glad I asked this question online so I could find the answer again.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply