I had to deploy a user defined function I was given yesterday, when I tried to I got the following error
Msg 6850, Level 16, State 1, Procedure fnGetBooks, Line 8
Column name ‘TEXT()’ contains an invalid XML identifier as required by FOR XML; ‘(’(0x0028) is the first character at fault.
The function looked a little like this one
CREATE FUNCTION fnGetBooks (@AuthorID INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @BookList VARCHAR(8000) SELECT @BookList =( SELECT BookName + ', ' AS [TEXT()] FROM Books WHERE AuthorID = @AuthorID AND BookName IS NOT NULL ORDER BY BookName FOR XML PATH('') ) RETURN LEFT(@BookList,(LEN(@BookList) -1)) END GO
Trying to run that will give you the same error. Do you see the problem? First I determined what 0x0028 was, you can easy do this by running the following query
As you can see, that is the left parentheses (
Interesting but not really helpful, I know I wrote stuff that uses FOR XML PATH myself in the past. I ran the following query to give me a list of objects that use FOR XML PATH
SELECT * FROM sys.objects WHERE OBJECT_DEFINITION(object_id) LIKE '%FOR%%XML%%PATH%'
Then I looked at some of those functions, the only thing I noticed is that TEXT was lowercase
SELECT BookName + ', ' AS [TEXT()]
SELECT BookName + ', ' AS [text()]
Once you make the change, the error will disappear. My suspicion is that some code formatting tool made it uppercase, perhaps the programmer copied and pasted it into some window and then packaged the output…I will have to follow up on that one
If you are interested what that function is used for, take a look at Concatenate Values From Multiple Rows Into One Column