Solved

create an xml string from db table

Posted on 2010-08-27
28
437 Views
Last Modified: 2013-12-16
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
0
Comment
Question by:COwebmaster
  • 15
  • 11
  • 2
28 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33547829
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
 

Author Comment

by:COwebmaster
ID: 33548296
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
 

Author Comment

by:COwebmaster
ID: 33548348
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33548802
==
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
 

Author Comment

by:COwebmaster
ID: 33549789
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33549816
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
 

Author Comment

by:COwebmaster
ID: 33549822
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
 

Author Comment

by:COwebmaster
ID: 33549827
ok, I see your point but then in the cfm output, how would that look?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33549834
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
 

Author Comment

by:COwebmaster
ID: 33549851
so you're saying just 1 table?  Do you have an example using the cfloop?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33550037
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33550045
And forgot to add you will have to account for the first row differently...with respect to .
0
 

Author Comment

by:COwebmaster
ID: 33551549
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551573
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
 

Author Comment

by:COwebmaster
ID: 33551581
ok, thanks Kevin!  I'll try out what you've suggested.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33552963
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33553070
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33553243
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33553255
Some implementations I believe can these days ...
0
 

Author Comment

by:COwebmaster
ID: 33562106
Hi everyone.  I am looking for a coldfusion solution only, not php
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33562978
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
 

Author Comment

by:COwebmaster
ID: 33563024
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
 

Author Comment

by:COwebmaster
ID: 33579697
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
 

Author Comment

by:COwebmaster
ID: 33580285
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
 

Author Comment

by:COwebmaster
ID: 33580405
seems like this code here is placing an extra </album> on the page..

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

Author Comment

by:COwebmaster
ID: 33580485
Ok, I added the following which fixed it..

...

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

..rest of it
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33580894
Yes, I mentioned that earlier but didn't show code.  You have to account for the first row not needing to close previous .
0
 

Author Closing Comment

by:COwebmaster
ID: 33580947
Thanks!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
C# Modify Read / Write / Update / Delete to  XML 2 67
Specific format 21 198
Problem to event 3 98
C# SQL BULK INSERT CLASS 5 48
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question