XML FeedsI 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
- SELECT CHAR(0x0028)
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
Instead of
- SELECT BookName + ', ' AS [TEXT()]
It was
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.