Retrieve data from SQL to display as XML

Question

My SQL query is this

  SELECT firstname AS 'User', count( * ) AS 'Number of photos uploaded'
    FROM members
    JOIN member_photo
   USING ( member_id )
GROUP BY firstname

Basically it is displaying data as follows:

User         Number of photos uploaded  
Az                   1  
Mz                   3

and so on..

What I want to do is transfer this data to a XML file in the form :

<photos>
    <user>Az</user>
    <user_photos>1</user_photos>
    <user>Mz</user>
    <user_photos>3</user_photos>
</photos>

or any other appropriate way.

My knowledge of XML is really low. I didn't try anything yet. What do you suggest? — Mozammil_K
– Mozammil_K, Commented Nov 13, 2011 at 10:50
The same as Pablo Santa Cruz: do the exact same thing you would do if it were html. — greg0ire
– greg0ire, Commented Nov 13, 2011 at 10:59

Pablo Santa Cruz · Accepted Answer · 2011-11-13 10:50:58Z

1

You do the exact same way you would do it if your XML was HTML.

Suppose this is your php script:

<?php
header('Content-Type: text/xml')'
?>
<photos>
<?php
$query = "select ... form foo...";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
?>
    <user><?= $row['user'] ?></user>
    <user_photos><?= $row['photos_count'] ?></user_photos>
<?php
}
?>
</photos>

answered Nov 13, 2011 at 10:50

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mozammil_K Over a year ago

and how do i output it to my XML file? I have created an XML file name statistics.xml

Pablo Santa Cruz Over a year ago

to an xml file!? I thought you wanted to output it to your browser. if you want to output to an XML file, you will need to open the file and write to it.

Mozammil_K Over a year ago

yeah exactly. Do you know how to do that? Thanks for the help :-)

user330315 · Accepted Answer · 2011-11-13 10:56:29Z

0

You did not state your DBMS, but the following will work with PostgreSQL and Oracle:

SELECT '<photos>'||xmlforest(firstname AS "user", count( * ) AS user_photos)||'</photos>'
FROM members
  JOIN member_photo USING ( member_id )
GROUP BY firstname

answered Nov 13, 2011 at 10:56

user330315

Collectives™ on Stack Overflow

Retrieve data from SQL to display as XML

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related