Post reply

Stumped trying to shred XML data into table rows

  • December 14, 2018 at 2:58 pm

    #2016366

    OK, so I made some progress. 

    Drew - the TRY_CAST returned NULL, so for whatever reason trying to cast the varchar(max) column directly into XML returns that error you said must be happening. That much still happens and confirms your original feedback. Thanks and sorry it I didn't explain the issue clearly to begin with. Possibly it is the "unable to switch the encoding" that I ran into below.

    However, I was able to get the full XML with brackets using something like the following. I still owe you all a self-contained working example but got sidetracked when I made the progress below. I will post the example as soon as I can. Although if anyone else runs into this issue and I haven't posted the example by that time, the pseudocode below should be a reasonable guide.

    --===== Reference for overall query:
    /*
    https://stackoverflow.com/questions/21738807/query-an-xml-stored-in-varchar-in-sql-server
    */

    --===== Set two variables, varchar(max), one xml.
    declare @ErrorXML as varchar(max);
    declare @x as xml;

    --===== Assign the table column data to the varchar(max) variable.
    select @ErrorXML = ColXML
    from MyTable
    where ColVarchar = 'Value';

    --===== I initially got the error below:
    /* Msg 9402, Level 16, State 1, Line 11
    XML parsing: line 1, character 38, unable to switch the encoding */

    --===== Replace the UTF encoding to fix the error, per this forum comment:
    /*
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a87f906e-c2d4-4fe0-958c-69367f71d675/xml-parsing-line-1-character-38-unable-to-switch-the-encoding?forum=sqlreplication
    */
    select @ErrorXML = replace(@ErrorXML, '"UTF-8"', '"UTF-16"');

    --===== Convert the varchar(max) variable data to nvarchar(max) then to xml.
    set @x = convert(xml, convert(varchar(max), @ErrorXML));

    --===== Output the result.
    select @x;

    Thanks again, everyone!

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • December 15, 2018 at 3:46 am

    #2016371

    webrunner - Friday, December 14, 2018 2:58 PM

    OK, so I made some progress. 

    Drew - the TRY_CAST returned NULL, so for whatever reason trying to cast the varchar(max) column directly into XML returns that error you said must be happening. That much still happens and confirms your original feedback. Thanks and sorry it I didn't explain the issue clearly to begin with. Possibly it is the "unable to switch the encoding" that I ran into below.

    However, I was able to get the full XML with brackets using something like the following. I still owe you all a self-contained working example but got sidetracked when I made the progress below. I will post the example as soon as I can. Although if anyone else runs into this issue and I haven't posted the example by that time, the pseudocode below should be a reasonable guide.

    --===== Reference for overall query:
    /*
    https://stackoverflow.com/questions/21738807/query-an-xml-stored-in-varchar-in-sql-server
    */

    --===== Set two variables, varchar(max), one xml.
    declare @ErrorXML as varchar(max);
    declare @x as xml;

    --===== Assign the table column data to the varchar(max) variable.
    select @ErrorXML = ColXML
    from MyTable
    where ColVarchar = 'Value';

    --===== I initially got the error below:
    /* Msg 9402, Level 16, State 1, Line 11
    XML parsing: line 1, character 38, unable to switch the encoding */

    --===== Replace the UTF encoding to fix the error, per this forum comment:
    /*
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a87f906e-c2d4-4fe0-958c-69367f71d675/xml-parsing-line-1-character-38-unable-to-switch-the-encoding?forum=sqlreplication
    */
    select @ErrorXML = replace(@ErrorXML, '"UTF-8"', '"UTF-16"');

    --===== Convert the varchar(max) variable data to nvarchar(max) then to xml.
    set @x = convert(xml, convert(varchar(max), @ErrorXML));

    --===== Output the result.
    select @x;

    Thanks again, everyone!

    webrunner

    Note that when converting string to XML, UTF-8 must be converted as varchar and UTF-16 must be converted as nvarchar, without the encoding directive, either can be used.
    😎

  • December 16, 2018 at 8:46 am

    #2016395

    A fellow by the name of Orlando Colamatteo posted a trick for displaying long strings a long time ago at the following URL...

    https://www.sqlservercentral.com/Forums/FindPost1468782.aspx

    Here's the function that I use to display long strings when I need to.  The cool part is, it's clickable in grid returns.


    CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
    Purpose:
    Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.

    Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
    to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however,
    handle most control-characters below ASCII 32.
    -----------------------------------------------------------------------------------------------------------------------
    Usage:
    --===== Example with Dynamic SQL
    DECLARE @sql VARCHAR(MAX);
    SELECT @sql = '
    SELECT somecolumnlist
     FROM some table with joins
    ;'
    ;
    SELECT LongString
     FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
    SELECT sm.Object_ID, Definition = ls.LongString
     FROM sys.SQL_Modules sm
    CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    -----------------------------------------------------------------------------------------------------------------------
    Credits:
    1. I learned this trick from a post by Orlando Colamatteo at the following link. It has served me very well since
      then. Thanks, Orlando.
      https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
    -----------------------------------------------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
       (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT LongString =
       (
       SELECT REPLACE(
           CAST(
            '--' + CHAR(10) + @pLongString + CHAR(10)
           AS VARCHAR(MAX))
          ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
         AS [processing-instruction(LongString)]
        FOR XML PATH(''), TYPE
       )
    ;
    GO

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 17, 2018 at 8:43 am

    #2016522

    Eirikur Eiriksson - Saturday, December 15, 2018 3:46 AM

    webrunner - Friday, December 14, 2018 2:58 PM

    OK, so I made some progress. 

    Drew - the TRY_CAST returned NULL, so for whatever reason trying to cast the varchar(max) column directly into XML returns that error you said must be happening. That much still happens and confirms your original feedback. Thanks and sorry it I didn't explain the issue clearly to begin with. Possibly it is the "unable to switch the encoding" that I ran into below.

    However, I was able to get the full XML with brackets using something like the following. I still owe you all a self-contained working example but got sidetracked when I made the progress below. I will post the example as soon as I can. Although if anyone else runs into this issue and I haven't posted the example by that time, the pseudocode below should be a reasonable guide.

    --===== Reference for overall query:
    /*
    https://stackoverflow.com/questions/21738807/query-an-xml-stored-in-varchar-in-sql-server
    */

    --===== Set two variables, varchar(max), one xml.
    declare @ErrorXML as varchar(max);
    declare @x as xml;

    --===== Assign the table column data to the varchar(max) variable.
    select @ErrorXML = ColXML
    from MyTable
    where ColVarchar = 'Value';

    --===== I initially got the error below:
    /* Msg 9402, Level 16, State 1, Line 11
    XML parsing: line 1, character 38, unable to switch the encoding */

    --===== Replace the UTF encoding to fix the error, per this forum comment:
    /*
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a87f906e-c2d4-4fe0-958c-69367f71d675/xml-parsing-line-1-character-38-unable-to-switch-the-encoding?forum=sqlreplication
    */
    select @ErrorXML = replace(@ErrorXML, '"UTF-8"', '"UTF-16"');

    --===== Convert the varchar(max) variable data to nvarchar(max) then to xml.
    set @x = convert(xml, convert(varchar(max), @ErrorXML));

    --===== Output the result.
    select @x;

    Thanks again, everyone!

    webrunner

    Note that when converting string to XML, UTF-8 must be converted as varchar and UTF-16 must be converted as nvarchar, without the encoding directive, either can be used.
    😎

    Interesting. Thanks again! I'm going to have to find some XML books/sites for some 101 and on training to make sure I understand this better. As I gathered from the extremely helpful responses from the folks here, XML is a lot more important to T-SQL than I had previously realized.

    Thanks again.
    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • December 17, 2018 at 8:44 am

    #2016523

    Jeff Moden - Sunday, December 16, 2018 8:46 AM

    A fellow by the name of Orlando Colamatteo posted a trick for displaying long strings a long time ago at the following URL...

    https://www.sqlservercentral.com/Forums/FindPost1468782.aspx

    Here's the function that I use to display long strings when I need to.  The cool part is, it's clickable in grid returns.


    CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
    Purpose:
    Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.

    Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
    to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however,
    handle most control-characters below ASCII 32.
    -----------------------------------------------------------------------------------------------------------------------
    Usage:
    --===== Example with Dynamic SQL
    DECLARE @sql VARCHAR(MAX);
    SELECT @sql = '
    SELECT somecolumnlist
     FROM some table with joins
    ;'
    ;
    SELECT LongString
     FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
    SELECT sm.Object_ID, Definition = ls.LongString
     FROM sys.SQL_Modules sm
    CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    -----------------------------------------------------------------------------------------------------------------------
    Credits:
    1. I learned this trick from a post by Orlando Colamatteo at the following link. It has served me very well since
      then. Thanks, Orlando.
      https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
    -----------------------------------------------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
       (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT LongString =
       (
       SELECT REPLACE(
           CAST(
            '--' + CHAR(10) + @pLongString + CHAR(10)
           AS VARCHAR(MAX))
          ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
         AS [processing-instruction(LongString)]
        FOR XML PATH(''), TYPE
       )
    ;
    GO

    Awesome. Thanks as always, Jeff!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • December 17, 2018 at 9:54 am

    #2016537

    As far as finding the offending character, your best bety might be.. to cheat 😛 .  As in - export the data using BCP to a XML file and open the file in freeware or open source XML editor and run the XML validation from there.  I use XMLSPY, which while not free does have a 30-day demo copy that will take you straight to the problem.

    In most cases it would be your common list of markup or formatting type characters, I.e. < > ' " &

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • December 17, 2018 at 10:06 am

    #2016539

    Michael Coles has a book called Pro SQL Server 2008 XML, published by Apress, don't even bother about any others for now (until I publish my book :exclamation:)
    From the top of my head, ISBN-10 1-4302-0630-6
    😎
    Trying to dig out the initial SQL Server XML implementation white-paper, got it somewhere but not on this system :pinch:

Viewing 7 posts - 16 through 21 (of 21 total)

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