[thomas_w]

Hallo Helmut,

ich denke, die Lösung 1 ist eine Sackgasse.

Zur Lösung 2 folgendes:

Nachdem die Tabellenstruktur komplett umgestellt ist, muss natürlich auch der SQL neu überdacht werden.

Mein Vorschlag wäre somit:

CREATE TABLE IF NOT EXISTS `tree` (
  `id` int(11) NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `tree`
--

INSERT INTO `tree` (`id`, `lft`, `rgt`) VALUES
(1, 1, 16),
(2, 2, 3),
(3, 4, 13),
(4, 5, 12),
(5, 6, 7),
(6, 8, 11),
(7, 9, 10),
(8, 14, 15);



CREATE TABLE IF NOT EXISTS `element` (
  `id` int(11) NOT NULL,
  `tag` varchar(100) NOT NULL,
  `desc` text NOT NULL,
  `id_tree` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `element`
--

INSERT INTO `element` (`id`, `tag`, `desc`, `id_tree`) VALUES
(1, 'A', 'content A', 1),
(2, 'B', 'content B', 2),
(3, 'C', 'content C', 3),
(4, 'D', 'content D', 4),
(5, 'E', 'content E', 5),
(6, 'F', 'content F', 6),
(7, 'G', 'content G', 7),
(8, 'H', 'content H', 8),
(9, 'G2', 'content G1', 7),
(10, 'G3', 'content G2', 7),
(11, 'C1', 'content C1', 3);


SELECT CASE r.flag
         WHEN 1 THEN CONCAT( '<' , r.tag, '>', r.`desc` )
         WHEN 2 THEN CONCAT( '</' , r.tag, '>')
         WHEN 3 THEN CONCAT( '<' , r.tag, '>', r.`desc`, '</' , r.tag, '>')
        END AS xml_teil
   FROM (SELECT e.tag, 1 AS flag, t.lft AS pt, e.`desc`
           FROM tree t
           JOIN element e 
             ON e.id_tree = t.id
          WHERE  1 = ( SELECT COUNT(*) 
                         FROM element e2 
                        WHERE e2.id_tree = e.id_tree
                     )             
             
         UNION ALL
         
         SELECT e.tag, 2 AS flag, t.rgt AS pt, e.`desc`
           FROM tree t
           JOIN element e 
             ON e.id_tree = t.id 
          WHERE  1 = ( SELECT COUNT(*) 
                         FROM element e2 
                        WHERE e2.id_tree = e.id_tree
                     )                          

         UNION ALL
        
         SELECT e.tag, 3 AS flag, t.rgt AS pt, e.`desc`
           FROM tree t
           JOIN element e 
             ON e.id_tree = t.id
          WHERE  1 < ( SELECT COUNT(*) 
                         FROM element e2 
                        WHERE e2.id_tree = e.id_tree
                     )
     
        ) r

ORDER BY r.pt, r.tag;

+---------------------+
| xml_teil            |
+---------------------+
| <A>content A        |
| <B>content B        |
| </B>                |
| <D>content D        |
| <E>content E        |
| </E>                |
| <F>content F        |
| <G>content G</G>    |
| <G2>content G1</G2> |
| <G3>content G2</G3> |
| </F>                |
| </D>                |
| <C>content C</C>    |
| <C1>content C1</C1> |
| <H>content H        |
| </H>                |
| </A>                |
+---------------------+
17 rows in set (0.00 sec)

mysql>

Grüße
Thomas