Retrieving “FOR XML AUTO” from MS SQL in CFML

Written by Peter Vejrum Terp on . Posted in Peters Blog

Today I was asked if it was possible to retrieve MS SQL XML in ColdFusion. My answer was of course that it must be possible. I had never done it before, but I couldn’t see why it should be a problem.

Welll!!! That wasn’t exactly as simple as expected. Here is what I tried and what happend and of course how to solve the issue

I had a query which looked something like:
SELECT TOP 100 * FROM mytable FOR XML AUTO;

This sql returns an XML object from MS SQL Server, which should be really easy to access. But I was wrong….

The name of the field with the XML was named “XML_F52E2B61-18A1-11d1-B105-00805F59916B” and if I changed just a minor thing in the sql it would have a new name. Then name of the field was dynamic. That in it self can be a problem, but can be solved by acessing the first column in  a result.

My first thought therefore was to retrieve the first row and the first column of the query in CFML, that was possible (I had to do some tricks but possible). Then the second problem occured – I didn’t get the full XML, only a part of it.

Well that’s solvable I just iterate the full result, cast the column as a string in ColdFusion, and adds each rows string in the end of each other. No problem! Well then the new problem came by, it wasn’t possible to convert the MS SQL XML datatype to a string – I tried a lot of different things, but nothing seemed to work.

A hole new approach was needed! Then a  college came with a brilliant idea – hack the result of the query with a query. We ended up doing the following code
<cfquery datasource=”MYDATASOURCE” name=”qXML”>
SELECT TOP 100 * FROM mytable FOR XML AUTO;
</cfquery>

<cfquery dbtype=”query” name=”xmlString”>
SELECT ‘xml’ AS xmlstring FROM qXML WHERE 0=1
UNION ALL
SELECT * FROM qXML
</cfquery>

<cfoutput query=”xmlString”>
#xmlstring#
</cfoutput>

This isn’t exactly a nice looking code, but it does the job and it’s fast. We simply cheat the sql – a very simple but very ugly solution. I hope this will help some of you CFML programmers out there!

Update
A lot simpler solution could be SELECT (SELECT TOP 100 * FROM mytable FOR XML AUTO) AS myResultField

Tags: , , , , ,

Trackback from your site.

Peter Vejrum Terp

My name is Peter Vejrum Terp, I am a travel geek, and tech hippie . I've been working as a professional Internet programmer, project manager and more since 2000. At the moment I work with SEO, and I've been doing it full time the last couple of years. I am cofounder of www.backpackerplanet.dk and www.backpackers-online.dk

Posts from my blog