How to show Carriage Return Char(13) in XML as real CR instead of tag.

  • Hi,

    I'm using a neat query to concatenate strings. It is using XML to do.

    I had 200.000 rows to be concatenated and the standard SQL was stalling.

    This statement is doing it in 0.4 seconds!! Amazing!

    One downside is dat the carriage return CHAR(13) is added as a tag being .

    I'm looking for a way to have it as a real CR.

    Anyone knows a way to do this?

    This is my test code:

    DECLARE @TABLE TABLE (ID INT IDENTITY(1,1),

    LINE VARCHAR(100))

    DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)

    DECLARE @OUTPUT VARCHAR(MAX) = ''

    INSERT @TABLE

    VALUES

    ('ONE'),

    ('TWO'),

    ('THREE'),

    ('FOUR'),

    ('FIVE'),

    ('SIX')

    SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))

    SELECT @OUTPUT

  • Be sure to display the output to text, not grid:

    DECLARE @TABLE TABLE (ID INT IDENTITY(1,1),

    LINE VARCHAR(100))

    DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10)

    DECLARE @OUTPUT VARCHAR(MAX) = ''

    INSERT @TABLE

    VALUES

    ('ONE'),

    ('TWO'),

    ('THREE'),

    ('FOUR'),

    ('FIVE'),

    ('SIX')

    select @OUTPUT = stuff((select LINE + char(13) + char(10)

    from @TABLE

    order by ID

    for xml path(''),TYPE).value('.','varchar(max)'),1,0,'')

    --SET @OUTPUT = (SELECT CAST( LINE AS VARCHAR(MAX) ) + @CRLF FROM @TABLE ORDER BY ID FOR XML PATH( '' ))

    SELECT @OUTPUT

  • The output is:

    ONE&#x0D ; TWO&#x0D ; THREE&#x0D ; FOUR&#x0D ; FIVE&#x0D ; SIX&#x0D ;

    I want to have the following output:

    ONE

    TWO

    THREE

    FOUR

    FIVE

    Best,

    Harry

  • Harry Drenth (3/28/2013)


    The output is:

    ONE TWO THREE FOUR FIVE SIX

    I want to have the following output:

    ONE

    TWO

    THREE

    FOUR

    FIVE

    Best,

    Harry

    Once again, do not display to a GRID, display to TEXT. It works.

  • Harry Drenth (3/28/2013)


    The output is:

    ONE TWO THREE FOUR FIVE SIX

    I want to have the following output:

    ONE

    TWO

    THREE

    FOUR

    FIVE

    Best,

    Harry

    Or, replace SELECT @OUTPUT with PRINT @OUTPUT

  • Unfortunately it doesn't

    It looks like this:

    (6 row(s) affected)

    -------------------------------------------------------------------

    ONE&#x0D ;

    TWO&#x0D ;

    THREE&#x0D ;

    FOUR&#x0D ;

    FIVE&#x0D ;

    SIX&#x0D ;

    (1 row(s) affected)

    The output is delivered to a VB.Net app which is saving the output to a file. The tag is in the output instead of the CHAR(13).

    In this Forum Post the tag is replace by a carriage. Therefore I added a space before the semi column (;).

    So the question remains how to prevend the XML statement to add the tag instead of CHAR(13).

    The easiest way is to replace it in the string with a replace statement but in some cases I concatenate over 200.000 lines so I have a lot to replace which costs time.

  • Harry Drenth (3/28/2013)


    Unfortunately it doesn't

    It looks like this:

    (6 row(s) affected)

    -------------------------------------------------------------------

    ONE&#x0D ;

    TWO&#x0D ;

    THREE&#x0D ;

    FOUR&#x0D ;

    FIVE&#x0D ;

    SIX&#x0D ;

    (1 row(s) affected)

    Post the code you are running. When I use what I posted I get this:

    ONE

    TWO

    THREE

    FOUR

    FIVE

    SIX

  • Harry Drenth (3/28/2013)


    Unfortunately it doesn't

    It looks like this:

    (6 row(s) affected)

    -------------------------------------------------------------------

    ONE&#x0D ;

    TWO&#x0D ;

    THREE&#x0D ;

    FOUR&#x0D ;

    FIVE&#x0D ;

    SIX&#x0D ;

    (1 row(s) affected)

    The output is delivered to a VB.Net app which is saving the output to a file. The tag is in the output instead of the CHAR(13).

    In this Forum Post the tag is replace by a carriage. Therefore I added a space before the semi column (;).

    So the question remains how to prevend the XML statement to add the tag instead of CHAR(13).

    The easiest way is to replace it in the string with a replace statement but in some cases I concatenate over 200.000 lines so I have a lot to replace which costs time.

    The tags I see in the post above are not from the output of my query but a result of posting on this forum.

  • Is the VB.NET app running the query itself, or is something else running it and passing the results to the application?

    If it is the second option, how is it passing the results?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Once again, please show us how you are using this. I use this method to build executable dynamic sql so I know it works.

  • Viewing 10 posts - 1 through 9 (of 9 total)

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