September 20, 2012 at 8:25 am
basic syntax issue here, and i'm brain dead apparently.
I've got a situation where i need to convert xml that has been htmlencoded back into regular text.
the xml is coming from a FOR XML command, and things like greater-than / less-than get htmlencoded into & l t ; and & g t ;
instead of nested REPLACE commands, i wanted to make a nifty function.
i need to convert them back into < and >, as well as anything else that was converted.
I've got a inline table value function, that features a recursive CTE I've adapted from an example.
Freaking cool because i'm using a recursive CTE, which i wanted to practice a bit more with.
If i limit the CTE Table MyData (FindText,ReplaceText) to less than 100 find/replace rows of data, it works fine, i don't hit the max recusion limit.
if i expand it to a larger set(the attached file has 202 find and replace values), i hit the max recursion error.
that's where i'm stuck..every time i try to adapt the code, i get a syntax error when i try to add OPTION (MAXRECURSION 250) for example.
someone please show me where i'm lost and where the stupid OPTION command would go....
because I'm replacing html elements, I cannot post the code on the forum, as it gets html-ized, so you can see the code in this link instead:
Link to Text File XML_to_ASCII.txt
Lowell
September 20, 2012 at 8:35 am
I would say it should look like this:
...
SELECT Text_Id, TextData
FROM FindReplaceCTE
WHERE LookupIdx = (SELECT MAX(LookupIdx)
FROM FindReplaceCTE)
option (maxrecursion 0) -- or what ever value you want to specify
September 20, 2012 at 8:38 am
agreed, that's where i thought it should go, but when i put it there i get a syntax error:
Msg 156, Level 15, State 1, Procedure XML_To_ASCII, Line 91
Incorrect syntax near the keyword 'option'.
maybe it's an issue where a function cannot have a query hint?
Lynn Pettis (9/20/2012)
I would say it should look like this:
...
SELECT Text_Id, TextData
FROM FindReplaceCTE
WHERE LookupIdx = (SELECT MAX(LookupIdx)
FROM FindReplaceCTE)
option (maxrecursion 0) -- or what ever value you want to specify
Lowell
September 20, 2012 at 8:47 am
yep, answering my own question, found a post from an MS spokesman:
Unfortunately, OPTION (MAXRECURSION limit) is not allowed in the definition
of views or user-defined functions. This restriction may be removed in a
future release.
--
Gail Erickson [MS]
SQL Server Documentation Team
i'll need to limit it to my top 100 expected find/replace values, but that's good to know.
Lowell
September 20, 2012 at 8:47 am
Only other option I can think of is at the end of the calling query.
September 20, 2012 at 9:19 am
Have you tried putting the option (maxrecursion 0) on the calling SQL?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply