create an xml string from db table

using coldfusion,how do I create an xml structure by looping through db records?  I want to return all active records using cfquery  and loop through and create the xml child nodes
COwebmasterAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You can have multiple tables, just use one query with a JOIN between the tables so you have one result set to use in .

http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-p79.htm#wp1101138

Code would look something like this:
<gallery>
  <cfset prvAlbumID = "" />
  <cfloop query="rsImages">
    <!--- set previous album to current --->
    <cfset curAlbumID = #album_id# />

    <cfoutput>
    <cfif curAlbumID neq prvAlbumID>
    </album> <!--- close previous album --->
    <!--- start new album --->
    <album title="#album_title#" description="#album_title#"
           lgpath="#album_lgpath#"   
           tnpath="#album_tnpath#" 
           tnsmpath="#album_tnsmpath#" 
           fspath="#album_fspath#" tn="#album_tn#">
    </cfif> <!--- otherwise, just output next image --->
      <img src="#img_path#" title="#img_title#" 
           caption="#img_caption#" link="#img_link#"/>
    </cfoutput>

    <!--- set previous album to current --->
    <cfset prvAlbumID = curAlbumID />
  </cfloop>
  </album>
</gallery>

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Here is a nice example on Adobe's site.
http://labs.adobe.com/technologies/spry/samples/utils/query2xml.html

Depending on the database you can also get XML in the first place as result of query, but as you can see from the reference it is very easy to construct XML document yourself.
0
 
COwebmasterAuthor Commented:
ok, I'm looking at those scripts now.  So I have the following xml script below.  how would I integrate what I have into that xml script on that site?
<?xml version="1.0" encoding="utf-8"?>
<gallery>
<album title="album title" description="album title" lgpath="../images/gallery/pictures/large/" tnpath="../images/gallery/pictures/thumb/" tnsmpath="../images/gallery/pictures/thumbsm/" fspath="../images/gallery/pictures/fs/" tn="">
<img src="image1.jpg" title="image title" caption="image" link=""/>
</album>
<album title="album title" description="album title" lgpath="../images/gallery/pictures/large/" tnpath="../images/gallery/pictures/thumb/" tnsmpath="../images/gallery/pictures/thumbsm/" fspath="../images/gallery/pictures/fs/" tn="">
<img src="image2.jpg" title="image title" caption="image" link=""/>
</album>
</gallery>

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
COwebmasterAuthor Commented:
In other words, I can create the table fields but what would I do to combine the following scripts together?
Mine:

<?xml version="1.0" encoding="utf-8"?>
<gallery>
<album title="album title" description="album title" lgpath="../images/gallery/pictures/large/" tnpath="../images/gallery/pictures/thumb/" tnsmpath="../images/gallery/pictures/thumbsm/" fspath="../images/gallery/pictures/fs/" tn="">
<img src="image1.jpg" title="image title" caption="image" link=""/>
</album>
<album title="album title" description="album title" lgpath="../images/gallery/pictures/large/" tnpath="../images/gallery/pictures/thumb/" tnsmpath="../images/gallery/pictures/thumbsm/" fspath="../images/gallery/pictures/fs/" tn="">
<img src="image2.jpg" title="image title" caption="image" link=""/>
</album>
</gallery>

Adobe Example:

<cfsetting enablecfoutputonly="yes">
<cfsetting showdebugoutput="no">
<!--- Query the database and get all the records from the Images table --->
<cfquery name="rsImages" datasource="dsImages">
 SELECT ID, AlbumName, ImagePath, ImageDescription, UploadDate FROM Images
</cfquery>
<!--- Send the headers --->
<cfheader name="Content-type" value="text/xml">
<cfheader name="Pragma" value="public">
<cfheader name="Cache-control" value="private">
<cfheader name="Expires" value="-1">
<cfsetting enablecfoutputonly="no"><?xml version="1.0" encoding="utf-8"?>
<images>
	<cfoutput query="rsImages">
	<image>
		<ID>#ID#</ID>
		<album><![CDATA[#AlbumName#]]></album>
		<path><![CDATA[#ImagePath#]]></path>
		<description><![CDATA[#ImageDescription#]]</description>
		<date><![CDATA[#UploadDate#]]></date>
	</image>
    </cfoutput>
</images>

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
==
Root element





This was flipped in the example as  with  inside, but essentially is the per record output, so the above is what you put inside the query loop or cfoutput.
<cfsetting enablecfoutputonly="yes">
<cfsetting showdebugoutput="no">
<!--- Query the database and get all the records from the Images table --->
<cfquery name="rsImages" datasource="dsImages">
  <!--- your actual query goes here --->
</cfquery>
<!--- Send the headers --->
<cfheader name="Content-type" value="text/xml">
<cfheader name="Pragma" value="public">
<cfheader name="Cache-control" value="private">
<cfheader name="Expires" value="-1">
<cfsetting enablecfoutputonly="no"><?xml version="1.0" encoding="utf-8"?>
<gallery>
  <cfoutput query="rsImages">
    <album title="#album_title#" description="#album_title#"
           lgpath="#album_lgpath#"   
           tnpath="#album_tnpath#" 
           tnsmpath="#album_tnsmpath#" 
           fspath="#album_fspath#" tn="#album_tn#">
      <img src="#img_path#" title="#img_title#" 
           caption="#img_caption#" link="#img_link#"/>
    </album>
  </cfoutput>
</gallery>

Open in new window

0
 
COwebmasterAuthor Commented:
ok, I'll try out.  now since I have different albums how would construct that in the sql?  so there are different albums and within those different albums different images.
0
 
Kevin CrossChief Technology OfficerCommented:
If you have a question with the SQL code you have so far, we can definitely help you tweak it.  We will need details on how the data is stored (i.e., one table or table of albums and table of images and linkage between them like albumid).  The platform is usually useful also. *smile*

Anyway, I will look out for that.

The approaches to consider:
+getting all the data as denormalized set of rows
i.e.,
album1 | image1
album1 | image2
album2 | image3
album2 | image5

Then you can sort the data by album, so that when you are looping you can simply check if the previous album id is equal to the current album id. If so, then just add img tag, if not then add end and begin album tag (just add the final end tag after the loop).

Alternatively, you could query the albums and then within that output loop query for the related images and display it in a nested loop.

Anyway, as I said, give it a try and post back any difficulties you run into.
0
 
COwebmasterAuthor Commented:
I guess I'll have to create a couple of db tables for the albums and images within those albums, yes?

album table:

albumID (primary key)
other fields..

image table:

imageID (primary key)
albumID (foreign key)
other fields..

So I construct my db tables like the above, how would adjust the code below?
<?xml version="1.0" encoding="utf-8"?>

<gallery>
<cfoutput query="rsImages">

<album title="#album_title#" description="#album_title#" lgpath="#album_lgpath#" tnpath="#album_tnpath#" tnsmpath="#album_tnsmpath#" fspath="#album_fspath#" tn="#album_tn#">
<img src="#img_path#" title="#img_title#" caption="#img_caption#" link="#img_link#"/>
<img src="#img_path#" title="#img_title#" caption="#img_caption#" link="#img_link#"/>
</album>

<album title="#album_title#" description="#album_title#" lgpath="#album_lgpath#" tnpath="#album_tnpath#" tnsmpath="#album_tnsmpath#" fspath="#album_fspath#" tn="#album_tn#">
<img src="#img_path#" title="#img_title#" caption="#img_caption#" link="#img_link#"/>
<img src="#img_path#" title="#img_title#" caption="#img_caption#" link="#img_link#"/>
<img src="#img_path#" title="#img_title#" caption="#img_caption#" link="#img_link#"/>
</album>

</cfoutput>
</gallery>

Open in new window

0
 
COwebmasterAuthor Commented:
ok, I see your point but then in the cfm output, how would that look?
0
 
Kevin CrossChief Technology OfficerCommented:
I would use  and do as instructed above. Personally, I would probably go with getting all the data denormalized so that basically I only make one call to db versus 1+{number of albums} calls.
0
 
COwebmasterAuthor Commented:
so you're saying just 1 table?  Do you have an example using the cfloop?
0
 
Kevin CrossChief Technology OfficerCommented:
And forgot to add you will have to account for the first row differently...with respect to .
0
 
COwebmasterAuthor Commented:
thanks mwvisa1.  So how could my sql tables look though if I separate out the albums and the images?  So in this..

<cfquery name="rsImages" datasource="dsImages">
  <!--- your actual query goes here --->
</cfquery>
0
 
Kevin CrossChief Technology OfficerCommented:
The SQL code would be dependent on your table structure and possibly your SQL platform.

typically it will be:

select a.album_id, ..., i.image_id, ...
from images i
join album a on a.album_id = i.album_id

If you have difficulties beyond this, please post a question with the details of your tables and the issue you are having to the database zone specific to your environment.  This will allow us to better assist you with the query.

Regards,

Kevin
0
 
COwebmasterAuthor Commented:
ok, thanks Kevin!  I'll try out what you've suggested.
0
 
Ray PaseurCommented:
I'm late to the party, but I have an excuse -- I was on vacation.  

On the XML side of things, there are a few PHP helpers to create the XML.  Most of them are documented under the SimpleXMLElement class.
http://us.php.net/manual/en/class.simplexmlelement.php

However as a practical matter I have found it easy to simply write the XML string myself using concatenation, spaces, tab characters and PHP_EOL.  Substituting values into the tags and attributes is as easy as using the double quote.  Just looking at the queries described in this thread leads me to believe that this will be the easiest way for you to produce your XML, too.

Once you have a query results set, print a row of it with var_dump() and we can probably suggest some easy ways to get that information into XML.

Best to all, ~Ray
0
 
Kevin CrossChief Technology OfficerCommented:
Ray,

I just noticed the inclusion on the PHP zone, but this is ColdFusion; hopefully, COwebmaster can confirm the intent of that zone on the question as I suspect it should be removed.

Kevin
0
 
Ray PaseurCommented:
Kevin, agreed - not sure why it was in PHP and Data Bases, unless there is some PHP component somewhere.  Can you even parse PHP on a ColdFusion machine?
0
 
Kevin CrossChief Technology OfficerCommented:
Some implementations I believe can these days ...
0
 
COwebmasterAuthor Commented:
Hi everyone.  I am looking for a coldfusion solution only, not php
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks for the confirmation, COwebmaster.  I asked a zone advisor I know to remove that zone from the question for you. *smile*

Is there anything else Ray and I could do to assist you?
0
 
COwebmasterAuthor Commented:
thanks for doing that.  I'll try out the example above and see if it works, then followup with any additional questions if I have any.
0
 
COwebmasterAuthor Commented:
not sure why but I get this error:

XML Parsing Error: no element found
Location: http://www.domain.net/gallery/pics/imagestest.cfm
Line Number 1, Column 1:
0
 
COwebmasterAuthor Commented:
ok, I had to comment out this line.. <cfheader name="Content-type" value="text/xml"> .. however, there appears to be an extra </album> at top which is causing the photo gallery to not display. Any idea how to fix that?
0
 
COwebmasterAuthor Commented:
seems like this code here is placing an extra </album> on the page..

<cfif curAlbumID neq prvAlbumID>
            </album> <!--- close previous album --->
0
 
COwebmasterAuthor Commented:
Ok, I added the following which fixed it..

...

<cfif curAlbumID neq prvAlbumID>
<cfif prvAlbumID neq "">
            </album> <!--- close previous album --->
            </cfif>

..rest of it
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, I mentioned that earlier but didn't show code.  You have to account for the first row not needing to close previous .
0
 
COwebmasterAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.