SQL Server with Mr. Denny

Feb 8 2010   11:00AM GMT

Using XMLNAMESPACES within a function

Denny Cherry Denny Cherry Profile: Denny Cherry

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.


<q1:Pattern xmlns:q1="AtiApi/xml/v1/schema/AtiApiSchema.xsd">
<q1:PatternId>1</q1:PatternId>
<q1:PatternText>hello world</q1:PatternText>
<q1:PatternName>hello world</q1:PatternName>
<q1:IsSystem>false</q1:IsSystem>
<q1:IsRegularExpression>false</q1:IsRegularExpression>
<q1:UserName>jfitch</q1:UserName>
<q1:CreateTimestamp>0001-01-01T00:00:00</q1:CreateTimestamp>
<q1:WholePhraseMatch>false</q1:WholePhraseMatch>
</q1:Pattern>

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.

Denny

3  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • LarrySmith
    I have arm-wrestled with this nasty bit of snytax for the last 2 hours. THANKS for posting this!!
    0 pointsBadges:
    report
  • Denny Cherry
    Be lucky, I spent a day or two working on this before I got it working correctly. There wasn't exactly a lot of reference material out there.
    66,065 pointsBadges:
    report
  • Manhtaitb
    If you are struggling with xml namespaces, there is a great tutorial on xpath namespaces at xml reports. It walks you through it in very simple steps [A href="http://www.xml-reports.com/2011/05/xml-namespaces-for-dummies-part-1.html"]xml reports[/A]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: