"I like to find solution for related tables."

mysql> select * from aa;
+------+--------+--------+--------+
| id   | field1 | field2 | field3 |
+------+--------+--------+--------+
|    1 | f      | b      | b      |
|    2 | q      | d      | b      |
+------+--------+--------+--------+
2 rows in set (0.00 sec)

SELECT aa.id, aa.field1, aa.field2, aa.field3,
       bb.type, bb.value
FROM aa
INNER JOIN bb ON aa.id = bb.id;

+------+--------+--------+--------+------+-------+
| 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)

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:

use strict;
use warnings;
use DBI;
use XML::Writer;
use Data::Dumper;

my $dbh    = DBI->connect( ... );
my $writer = XML::Writer->new();

$writer->xmlDecl('UTF-8');
$writer->doctype('xml');
$writer->startTag('xml');

my $aa_sql = 'select id,field1,field2,field3 from AA';
my $bb_sql = 'select type, value from BB where id = ?';

for my $row (@{$dbh->selectall_arrayref($aa_sql)}) {
   $writer->startTag('foo',id => $row->[0]);
   $writer->dataElement("field$_",$row->[$_]) for 1..3;

   # warning: i should check for 'bars' before i start a tag
   $writer->startTag('bars');
   for $_ (@{$dbh->selectall_arrayref($bb_sql,undef,$row->[0])}) {
      $writer->dataElement('bar',$_->[1],type => $_->[0]);
   }
   $writer->endTag('bars');
   $writer->endTag();
}

$writer->endTag('xml');
$dbh->disconnect;
[download]

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)