artist has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
This could a fairly generic database to XML problem.
I have 2 tables in mysql database with following columns
1. ID, field1,field2,field3
2. ID,type,value
In table 1, ID is the key and in table 2, ID can appear multiple times. Sample data for table 2: 
1,X,abc
1,X,pqrs
1,Y,mno
2,Z,pqr
2,X,abcd
2,X,apry
I like to to produce an XML strcture which correctly represents records with ID only once and with all the other related data. How I would go about it?

For single table, this could be very easy exercise. DBIx::XML_RDB as in How do I dump a database table into XML format?. I like to find solution for related tables.

Thanks
artist

Replies are listed 'Best First'.
Re: XML Conversion of Tables
by mirod (Canon) on Jul 14, 2003 at 19:03 UTC

    DBIx::XML_RDB and its successor, XML::Generator::DBI will let you export a query from the DB, so it is just a matter of giving it the proper query, using a left joint I believe.

[reply]
      I would appreciate any example. This is going to be multi level XML stucture. Quering would give me either single row or multiple row which may not serve the exact purpose here.

      artist

[reply]
(jeffa) Re: XML Conversion of Tables
by jeffa (Bishop) on Jul 14, 2003 at 22:10 UTC

    "I like to find solution for related tables."

    That's not a Perl request. That is a database design request. And with your current design, i don't see how you can get what you want, but given the data from your second table (which i will call bb) and this data for the first table (which i will call aa): 
    mysql> select * from aa;
+------+--------+--------+--------+
| id   | field1 | field2 | field3 |
+------+--------+--------+--------+
|    1 | f      | b      | b      |
|    2 | q      | d      | b      |
+------+--------+--------+--------+
2 rows in set (0.00 sec)
    You can use the following SQL statement to join the two tables: 
    
SELECT aa.id, aa.field1, aa.field2, aa.field3,
       bb.type, bb.value
FROM aa
INNER JOIN bb ON aa.id = bb.id;
    This yields: 
    +------+--------+--------+--------+------+-------+
| id   | field1 | field2 | field3 | type | value |
+------+--------+--------+--------+------+-------+
|    1 | f      | b      | b      | X    | a     |
|    1 | f      | b      | b      | X    | p     |
|    1 | f      | b      | b      | Y    | m     |
|    2 | q      | d      | b      | Z    | a     |
|    2 | q      | d      | b      | X    | a     |
+------+--------+--------+--------+------+-------+
5 rows in set (0.00 sec)
    (looks like i got some of your data wrong :P ... anyhoo)

    Why the dupes? Because of the design. If you really want that ID only once, you will have to either fix your design or use a module that forces allows you to intervene with the XML creation process. Yuck. I'd rather fix the design then code something like:

    Read more... (1212 Bytes)
    But again, since i don't know what you are trying to do (man you sure are cryptic in your questions!) ... i coded something silly (again).

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]

Back to Seekers of Perl Wisdom