Column name contains an invalid XML identifier as required

  • Fraggle-805517

    SSCrazy Eights

    Points: 8907

    Hey everyone. Weird error today that I haven't seen before and can't seem to find a lot on. Anyone see anything in my code below that would cause the following error to occur?

    Column name 'TEXT()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

    UPDATE t

    SET InventoryList = SUBSTRING(tmp2.Products, 1, LEN(tmp2.products) - 1)

    FROM #temp AS t

    INNER JOIN (SELECT List AS Products, t.BCKey

    FROM #temp AS t

    CROSS APPLY(SELECT CONVERT(NVARCHAR(30), i.productid) + '|' AS [TEXT()]

    FROM orders AS o WITH(NOLOCK)

    INNER JOIN orderstatus AS os WITH(NOLOCK) ON o.statusid = os.statusid

    INNER JOIN orderdetails AS od WITH(NOLOCK) ON o.orderid = od.orderid

    INNER JOIN inventory AS I WITH(NOLOCK) ON od.inventoryid = i.inventoryid

    INNER JOIN inventorymisc AS im WITH(NOLOCK) ON i.inventoryid = im.inventoryid

    WHERE o.subjectid = t.bckey

    AND os.official = 1

    AND secondarysubjecttypeid = 0

    AND (im.DistributorSignupPack = 1

    OR im.distributorUpgradepack = 1

    OR gfRanktypeid IS NOT NULL

    )

    AND o.bonusdate BETWEEN @qualifyStart AND @qualifyEnd

    FOR XML PATH('')

    ) AS DUMMY(List)

    WHERE list IS NOT NULL

    AND t.ID = @bonusrunid

    ) AS TMP2 ON TMP2.BCKey = T.BCKey

    WHERE t.ID = @bonusrunid

    Thanks,

    Fraggle

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Have you tried changing this, 'TEXT()' , to this 'TEXT'?

  • Fraggle-805517

    SSCrazy Eights

    Points: 8907

    Actually, I was able to figure out the error thanks to help from a .NET guy (embarrassing ain't it). :ermm: TEXT() should be text(). The only thing he couldn't tell me what why this was the case. Thoughts?

    Nathan

  • boumerlin

    SSCommitted

    Points: 1963

    Actually, I was able to figure out the error thanks to help from a .NET guy (embarrassing ain't it). TEXT() should be text(). The only thing he couldn't tell me what why this was the case. Thoughts?

    Yes...I believe that is because text() represents an XML data type and XML is very case-sensitive!:-P

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

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