June 1, 2009 at 12:45 pm
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
June 1, 2009 at 1:07 pm
Have you tried changing this, 'TEXT()' , to this 'TEXT'?
June 1, 2009 at 1:11 pm
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
August 6, 2009 at 11:59 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply