Home All advices
ENPL
Modify date: 2013-01-15 11:39:14

Export data from table into xml

Below are shown how export data from table into xml

sample tables

l_idname
1 Premier League
2 Primera Division
3 Serie A
4 Bundesliga
5 Ligue 1
t_idl_idname
4 1 Arsenal Londyn
3 1 Chelsea Londyn
5 1 Liverpool FC
2 1 Manchester City
1 1 Manchester United
7 2 Atletico Madryt
6 2 FC Barcelona
8 2 Real Madryt
9 2 Valencia CF
14 3 AC Milan
13 3 AS Roma
11 3 Inter Mediolan
10 3 Juventus FC
12 3 Lazio Rzym
16 4 Bayer Leverkusen
15 4 Bayern Monachium
17 4 Borussia Dortmund
18 4 Schalke 04 Gelsenkirchen
22 5 Girondins Bordeaux
19 5 Olympique Lyon
20 5 Olympique Marsylia
21 5 Paris Saint Germain

example of a solution

SELECT
   CONCAT(
   '<leagues>',
    GROUP_CONCAT('<league name="', l.l_name, '">',
    (
        SELECT
            CONCAT('<teams>',
            GROUP_CONCAT('<team name="', t.t_name, '"/>'  SEPARATOR ''),
            '</teams>')
        FROM teams t
        GROUP BY t.l_id
        HAVING t.l_id = l.l_id
    ),
    '</league>'  SEPARATOR ''),
    '</leagues>') AS xmldocAS
FROM leagues l;

result xml

<leagues>
<league name="bundesliga">
  <teams>
    <team name="bayer leverkusen"/>
    <team name="bayern monachium"/>
    <team name="borussia dortmund"/>
    <team name="schalke 04 gelsenkirchen"/>
  </teams>
</league>
<league name="ligue 1">
  <teams>
    <team name="girondins bordeaux"/>
    <team name="olympique lyon"/>
    <team name="olympique marsylia"/>
    <team name="paris saint germain"/>
  </teams>
</league>
<league name="premier league">
  <teams>
    <team name="arsenal londyn"/>
    <team name="chelsea londyn"/>
    <team name="liverpool fc"/>
    <team name="manchester city"/>
    <team name="manchester united"/>
  </teams>
</league>
<league name="primera division">
  <teams>
    <team name="atletico madryt"/>
    <team name="fc barcelona"/>
    <team name="real madryt"/>
    <team name="valencia cf"/>
  </teams>
</league>
<league name="serie a">
  <teams>
    <team name="ac milan"/>
    <team name="as roma"/>
    <team name="inter mediolan"/>
    <team name="juventus fc"/>
    <team name="lazio rzym"/>
  </teams>
</league>
</leagues>
[ Strona główna ]