Posted by: Denny Cherry
Functions, T/SQL, XML
Recently one of my developers asked me to begin returning data from a function as XML instead of a comma separated list. My thoughts on this was awesome, another cursor getting removed from the system. I’ll write a quick FOR XML AUTO, ROOT(‘root’) at the bottom of the SELECT statement being used to generate the cursor and I’ll be done. Right? Wrong!
Apparently the developer wanted the XML to come back looking like this.
Now that looks kind of like the XML that the SQL Server would normally generate, except for the q1 prefix and the path to the xsd. He said that if I wanted to wrap all this in a … tag that was fine as well. Well isn’t that nice of him.
It took me a few minutes to find the XMLNAMESPACES statement. If you aren’t familiar with this little gem, basically it allows you to assign an XSD to the XML which is being contained with the XML Document. (Yes I’m aware that isn’t an internet valid XSD, it’s a relative path within our code base. At least that is what the .NET guys told me.)
Now it didn’t take long to get the query to output the way he wanted to when run manually. However the little catch here is that this runs from within a scalar function so I can’t just have a select statement returning my XML document. I have to return the XML document via a RETURN statement. We use a function because this XML document is returned as a column within a recordset so a function was pretty much the old option short of a nested query which would just be damned ugly.
I tried all permutations of code I could think so to make it stick the XML document into a variable, but nothing would work without throwing some sort of error message or another. BOL was not very helpful on this one, and sadly neither was my good friend Michael Coles book on SQL Server 2008 XML (which has answered every single XML question I’ve had so far). I finely broke down and posted my question on Twitter to see if anyone else had tried to do this in the past.
What I got back within just a few minutes from Mladen Prajdic was this little snip of code (it has to be short if it’ll fit in a twitter message). Now keep in mind that I think of Mladen as a good friend, as I speak with him almost daily, yet we’ve never met (I think) as he lives in Ljubljana, Slovenia (take a moment and look at it on a map, I had to, apparently its north/east of Italy).
declare @xml xml; WITH XMLNAMESPACES ('uri' as ns1) select @xml = (your for xml select); select @xml
It was as if the skies had opened and gold was being rained down upon me. All you perverts (and you know who you are, I’ll name you here if I must) can get your minds out of the gutter now, for the rest please keep reading.
What I ended up with was the T/SQL below which made my function work and my .NET guy happy.
ALTER FUNCTION [dbo].[GetEmailAlertWordXRef] (@EmailId int) RETURNS XML AS BEGIN DECLARE @RetVal XML; WITH XMLNAMESPACES('AtiApi/xml/v1/schema/AtiApiSchema.xsd' AS q1) SELECT @RetVal = ( SELECT 'q1:PatternId' = Keyphrase.KeyphraseId, 'q1:PatternText' = Keyphrase.KeyphraseText, 'q1:PatternName' = ISNULL(Keyphrase.KeyphraseName, Keyphrase.KeyphraseText), 'q1:IsSystem' = KeyphraseType.IsSystem, 'q1:IsRegularExpression' = KeyphraseType.IsRegEx, 'q1:UserName' = '', 'q1:CreateTimestamp' = Keyphrase.CreateTimestamp, 'q1:WholePhraseMatch' = 0 FROM EmailKeyphrase INNER JOIN Keyphrase ON EmailKeyphrase.KeyphraseId=Keyphrase.KeyphraseId INNER JOIN KeyphraseType ON Keyphrase.KeyphrasetypeId = KeyphraseType.KeyphraseTypeID WHERE EmailKeyphrase.EmailId = @EmailId ORDER BY Keyphrase.KeyphraseText FOR XML PATH('q1:Pattern'), ROOT('root')); RETURN @RetVal END GO
Since I couldn’t really find anything on the net with my searching I figured I’d fix that problem.
Twitter to the rescue again.