oracle et xml

**r83** · 15/10/2009, 21h15

Bonjour,

j'ai les tables suivantes :
Produit(id prod, libelle, puht) pk:idprod
Commande(idcde, datecde) pk:idcde
Prodcde (idprod,idcde, qte) pk:idcde,idprod
plus les fk.

J'aimerais sortir un fichier xml du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
<lescommandes>
	<commande id=123>
		<nomcli> Dupont</nomcli>
		<datecde> 12/03/2009</datecde>
		<lignesCde>
			<idprod id='P1'>
				<Qte>12</qtre>
			</idprod>
			<idprod id='P2'>
				<Qte>6</qtre>
			<idprod>
		<lignesCde>
	</commande>
		<commande id=124>
		<nomcli> Durand</nomcli>
		<datecde> 15/05/2009</datecde>
		<lignesCde>
			<idprod id='P3'>
				<Qte>5</qtre>
			</idprod>
			<idprod id='P2'>
				<Qte>4</qtre>
			<idprod>
			<idprod id='P2'>
			<Qte>4</qtre>
			<idprod>
		<lignesCde>
	</commande>
</lescommandes>

J'ai bien réussi à sortir un semblant de XML, mais les éléments se répètent.
Dans le curseur retourné, j'aimerais que les lignes ci-dessus apparaissent en l'état...
J'ai testé XMLElement et XMLagg, mais chaque ligne correspond au gropu by, ce que je ne veux pas.
Merci pour votre aide

**mnitu** · 16/10/2009, 08h58

Postez un petit jeu d'essaie: ordre sql de création des tables plus les inserts de quelques lignes pour chaque table, le résultat attendu et votre requête.

**r83** · 16/10/2009, 10h11

Bonjour,

Je donne le code en espérant que ce ne soit pas trop long :
Les tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
drop table commande cascade constraints;
drop table produit cascade constraints;
drop table prodcde cascade constraints;
create table produit(
idprod  integer primary key,
libprod varchar(50) not null,
puprod decimal(6,2) not null
);
/
create table commande(idcde integer primary key, 
datecde date not null);
/
create table Prodcde (idprod integer references produit(idprod),
idcde integer references commande(idcde), 
qte integer default 1 not null,
constraint pk_prodcde primary key(idprod, idcde));
/
 
insert into produit (idprod,libprod,puprod)  values (100,'stylo',55);
insert into produit (idprod,libprod,puprod)  values (101,'gomme',114);
insert into produit (idprod,libprod,puprod)  values (102,'règle',13.9);
 
insert into commande(idcde, datecde) values (1234, sysdate);
insert into commande(idcde, datecde) values (1235, sysdate);
 
insert into prodcde(idcde, idprod, qte) values (1234,100,12);
insert into prodcde(idcde, idprod, qte) values (1234,101,8);
insert into prodcde(idcde, idprod, qte) values (1235,100,6);
insert into prodcde(idcde, idprod, qte) values (1235,101,14);
insert into prodcde(idcde, idprod, qte) values (1235,102,9);
 
commit;
select * from commande;
select * from prodcde;
select * from produit;

La requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT (XMLElement(name "commande", 
                  XMLAttributes(commande.idcde as id), 
                  XMLElement(name "datecommande",datecde), 
                  XMLElement(name "LigneCde",null),  
                  XMLForest(idprod as "id", libprod as "libelle", qte as "qteCommande")
	          )).getStringVal() as RESULTAT
FROM commande inner join prodcde on commande.idcde=prodcde.idcde
inner join produit on prodcde.idprod=produit.idprod
order by commande.idcde;

Le résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
RESULTAT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<commande ID="1234"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>101</id><libelle>gomme</libelle><qteCommande>8</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1234"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>100</id><libelle>stylo</libelle><qteCommande>12</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>102</id><libelle>règle</libelle><qteCommande>9</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>100</id><libelle>stylo</libelle><qteCommande>6</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
<commande ID="1235"><datecommande>2009-10-16</datecommande><LigneCde></LigneCde><id>101</id><libelle>gomme</libelle><qteCommande>14</qteCommande></commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
 
5 rows selected

et voilà ce que j'aurais aimé avoir (et avant tout si je peux espérer obtenir ça (en dehors de la mise en page

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
RESULTAT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<commande ID="1234">
	<datecommande>2009-10-16</datecommande>
	<LigneCde>
		<id>101</id>
		<libelle>gomme</libelle>
		<qteCommande>8</qteCommande>
	</LigneCde>	
	<LigneCde>	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
		<id>100</id>
		<libelle>stylo</libelle>
		<qteCommande>12</qteCommande>
	</LigneCde>
</commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
<commande ID="1235">
	<datecommande>2009-10-16</datecommande>
	<LigneCde>
		<id>102</id>
		<libelle>règle</libelle>
		<qteCommande>9</qteCommande>
	</LigneCde>
	<LigneCde>
		<id>100</id>
		<libelle>stylo</libelle>
		<qteCommande>6</qteCommande>
	</LigneCde>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
	<LigneCde>
		<id>101</id>
		<libelle>gomme</libelle>
		<qteCommande>14</qteCommande
	</LigneCde>
</commande>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
 
5 rows selected

merci pour vos réponses
Bonne journée

**mnitu** · 16/10/2009, 11h04

Voilà une solution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT (XMLElement(name "commande", 
                  XMLAttributes(commande.idcde AS id), 
                  XMLElement(name "datecommande",datecde), 
                  (Select XmlAgg(XmlElement("LigneCde",
                                              XMLForest(produit.idprod AS "id", 
                                                        libprod AS "libelle", 
                                                        qte AS "qteCommande")
                                           )
                                 )                                 
                     From prodcde 
                          Inner Join produit
                       On (prodcde.idprod=produit.idprod) 
                    Where prodcde.idcde = commande.idcde
                  )
            )).extract('/').getstringval() AS RESULTAT
FROM commande 
ORDER BY commande.idcde
/

Y en a, bien sûr, des autres.

**Waldar** · 16/10/2009, 11h12

On peut se passer du sous-select scalaire en utilisant les propriétés d'agrégat de XMLAgg, ce qui donne je pense un meilleur plan d'exécution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SELECT
    XMLElement(name "commande",
               XMLAttributes(cm.idcde AS id),
               XMLElement(name "datecommande", cm.datecde), 
               XMLAgg(XMLElement(name "LigneCde",
                                 XMLForest(pr.idprod AS "id",
                                           pr.libprod AS "libelle",
                                           pc.qte AS "qteCommande"))
	          )).getstringval() AS RESULTAT
FROM
    commande cm
    INNER JOIN prodcde pc
      ON pc.idcde = cm.idcde
    INNER JOIN produit pr
      ON pr.idprod = pc.idprod
GROUP BY
    cm.idcde,
    cm.datecde
ORDER BY
    cm.idcde asc;
 
 
<commande ID="1234">
  <datecommande>2009-10-16</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>12</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>8</qteCommande>
  </LigneCde>
</commande>
 
<commande ID="1235">
  <datecommande>2009-10-16</datecommande>
  <LigneCde>
    <id>100</id>
    <libelle>stylo</libelle>
    <qteCommande>6</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>102</id>
    <libelle>règle</libelle>
    <qteCommande>9</qteCommande>
  </LigneCde>
  <LigneCde>
    <id>101</id>
    <libelle>gomme</libelle>
    <qteCommande>14</qteCommande>
  </LigneCde>
</commande>

**r83** · 16/10/2009, 11h33

Super, je suis heureux d'apprendre que c'est faisable!!!
Je comprends mieux la solution de la solution de Waldar que celle de mnitu, où je n'arrive pas bien à déchiffrer la vue scalaire. Je vais travailler dessus.
Avez-vous un bon tuto là-dessus, je n'ai rien de très probant sur le net (en français???).
Je suppose que l'expression : .getstringval() sert à renvoyer le résultat sous forme de chaine de caractères, mais par définition il renverrait une chaine non?
Et pourquoi l'expression : .extract('/').getstringval()
Encore un grand merci pour votre aide
Bonne journée

**Waldar** · 16/10/2009, 12h06

Il y a deux pdf d'Atkins Ken qui sont très très bons, je n'ai plus le site sous la main.

oracle et xml

Oracle

Vue hybride

Discussions similaires

Partager

Partager