Solved

create an xml string from db table

Posted on 2010-08-27
28
427 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

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

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 108

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now