Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create an xml string from db table

Posted on 2010-08-27
28
Medium Priority
?
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 11
  • 2
28 Comments
 
LVL 60

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 60

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 60

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 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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 60

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 60

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 111

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 60

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 111

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 60

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 60

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 60

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

722 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