CAST FOR XML - Illegal Name Character

  • jamie_collins

    Ten Centuries

    Points: 1182

    I am trying to write a query that generates an html report to be emailed and one of the columns is a hyperlink to our report server that we can click on from the email and it will open the report in report browser.  It needs to pass the row value as a parameter to the report:

    SELECT @xml = '

    Dealer Loss Ratio Report


    The following dealers (100+ Warranties Sold) have a current Loss Ratio greater than 35%


    <table>' +

    (SELECT 'Dealer' th
    ,'Warranty Count' th
    ,'Total Sales' th
    ,'Claims Count' th
    ,'Total Claims' th
    ,'Avg Claim' th
    ,'Loss Ratio' th
    FOR XML RAW('tr'), ELEMENTS) +
    (SELECT CAST('' AS XML) td
    ,SUM(warcount) td
    ,FORMAT(SUM(RevenueRemittedAmount), 'C') td
    ,SUM(ClaimsCount) td
    ,FORMAT(SUM(ClaimsAmount), 'C') td
    ,FORMAT(SUM(ClaimsAmount) / SUM(ClaimsCount), 'C') td
    ,FORMAT(SUM(ClaimsAmount) / SUM(RevenueRemittedAmount), 'P') td
    FROM [GlobalWarrantyReports].[dbo].[WarrantyDetail]
    WHERE WarrantyStatus IN ('Active')
    AND RevenueRemittedAmount <> 0
    GROUP BY Dealer
    HAVING SUM(warcount) >= 100 AND (SUM(ClaimsAmount) / SUM(RevenueRemittedAmount)) >= .35
    ORDER BY 7 DESC
    FOR XML RAW('tr'), ELEMENTS) + '</table>'

    I am getting the error illegal name character.  It's coming from the Dealer column because I can hardcode a name and it works fine.

    Not sure what I am missing.

    Thanks for any and all suggestions.

  • Phil Parkin

    SSC Guru

    Points: 243596

    Do any of your dealer names contain an ampersand (&)? That might do it.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • jamie_collins

    Ten Centuries

    Points: 1182

    I think you're right.

    That column has a ton of special characters.  Might just abandon this and put a link to the main report in the email that they can drill through to.

    Thanks.

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

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