Help with SELECT STUFF and FOR XML to include Carriage Return for Null records

  • Hi everyone,

    I have been struggling with this T-SQL for much too long today.  I am using the SELECT STUFF with FOR XML to present multiple rows into one field.  However, when I Left Join a table and get Null results for some rows, I am unable to get the carriage return to work correctly.

    For example, my return result set is:
    Null
    Value1
    Null
    Value2

    I would like my query to put a carriage return for the first Null, then Value1 and carriage return, then carriage return for the next Null, and then the final Value2, thus keeping the spacing of the result set, which I will need to display another row that does have all four values.  Essentially, I am trying to handle a subreport functionality by concatenating the results into one row.

    My sample query is:
       SELECT STUFF((SELECT ISNULL(t2.Column2_Name, '') + CHAR(10)
        FROM [dbo].[Table1] t1
        LEFT JOIN Table2 t2 ON t1.Column1_ID = t2.Column2_ID
        ORDER BY t1.Column1_ID 
        FOR XML PATH('')),1,0,'') 

    The Left Join produces Nulls in the results, and I need to be able to keep results spaced appropriately with carriage returns.

    Any help would be greatly appreciated,
    Tom

  • Carriage returns and line feeds together make for a standard ASCII based line ending.   Not sure if it will help, but wherever CHAR(10) appears, it probably should be CHAR(13) + CHAR(10).

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Hi, and thank you so much for taking the time to reply.  It reminded me about my original post!  

    I was able to cobble a solution together, ugly, but works.  I will post below if anyone else runs into this.  I ended up using the REPLACE command to catch the extra characters I did not want, because it appeared they were coming back as strings instead of being recorded as carriage returns. The new code is in bold.

    SELECT REPLACE(STUFF((SELECT ISNULL(t2.Column2_Name, '') + CHAR(10) 
    FROM [dbo].[Table1] t1
    LEFT JOIN Table2 t2 ON t1.Column1_ID = t2.Column2_ID
    ORDER BY t1.Column1_ID 
    FOR XML PATH('')),1,0,'') ,' ','')

  • One last thought... carriage return is actually CHAR(13), not CHAR(10).   However, in the world of ASCII, they typically appear in order CHAR(13) followed by CHAR(10).   The latter is the New Line (aka Line Feed) character.   Wondering if the tool being used to "see" the data was impacted by the interpretation?

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • The following approach is better, because there are other characters that may be entitized in XML and this approach will handle all of them in a single step.

    SELECT STUFF((SELECT ISNULL(t2.Column2_Name, '') + CHAR(10)
    FROM [dbo].[Table1] t1
    LEFT JOIN Table2 t2 ON t1.Column1_ID = t2.Column2_ID
    ORDER BY t1.Column1_ID
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')

    The TYPE directive ensures that the value is returned as XML so that you can use the .value method to convert to nvarchar (including converting entitized characters back to a more legible form).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Drew:  Thank you, I was noticing that when I was researching on the web.

    Steve:  I am displaying the view in a custom ActiveReports project/viewer.  And fortunately, it rendered fine with the hack.  I know I had tried CHAR(13) earlier, but I will play around with it a bit more and let you know if anything works better (cleaner).

  • LineBreak does not work in STUFF. You can use this trick :

    SELECT REPLACE( STUFF((SELECT ISNULL(t2.Column2_Name, '') + '##@@$$$'
    FROM [dbo].[Table1] t1
    LEFT JOIN Table2 t2 ON t1.Column1_ID = t2.Column2_ID
    ORDER BY t1.Column1_ID
    FOR XML PATH('')),1,0,'') , '##@@$$$', char(13)+Char(10))

Viewing 7 posts - 1 through 7 (of 7 total)

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