Oracle/XML avec une requete imbriquée

Version imprimable

09/07/2007, 11h30
FABFAB125

Oracle/XML avec une requete imbriquée

Bonjour à tous,

y à t'il une possibilité de sélectionner directement à partir d'une requête imbriquée ( de style table avec une jointure avec une sous table) la structure XML adéquate

exemple de requete :

select tableMaitre.champ1, tableEsclave.Champ2
from tableMaitre, tableEsclave
where tableEsclave.foreignkey=tableMaitre.Key

la structure xml que je voudrais avoir est :
ROWSET>
<ROW num="1">
<champ1>donneechamp1
<Champ2>donneechamp2</Champ2>
</champ1>
</ROW>
</ROWSET>

j'ai essayer avec dbms_XMLQuery.getXML mais j'ai toujours une structure simple

merci pour votre aide

Code:

1
2
3
 
select 
dbms_xmlgen.getxml('select tableMaitre.champ1, tableEsclave.Champ2 from tableMaitre, tableEsclave where tableEsclave.foreignkey=tableMaitre.Key') from dual;

PS: tu veux que champ2 fasse partie de champ1?

que penses-tu de

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select 
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(deptno),
        xmlagg(sys_xmlgen(empno))))) 
from emp 
group by deptno;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
<EMP>
  <DEPT DEPTNO="10">
    <EMPNO>7782</EMPNO>
    <EMPNO>7839</EMPNO>
    <EMPNO>7934</EMPNO>
  </DEPT>
  <DEPT DEPTNO="20">
    <EMPNO>7369</EMPNO>
    <EMPNO>7902</EMPNO>
    <EMPNO>7876</EMPNO>
    <EMPNO>7788</EMPNO>
    <EMPNO>7566</EMPNO>
  </DEPT>
  <DEPT DEPTNO="30">
    <EMPNO>7499</EMPNO>
    <EMPNO>7698</EMPNO>
    <EMPNO>7654</EMPNO>
    <EMPNO>7844</EMPNO>
    <EMPNO>7900</EMPNO>
    <EMPNO>7521</EMPNO>
  </DEPT>
</EMP>

10/07/2007, 10h16
FABFAB125
Bonjour,
mais cette commande donne la structure suivante :

ROWSET>
<ROW num="1">
<champ1>donneechamp1</champ1>
<Champ2>donneechamp2</Champ2>
</ROW>
</ROWSET>
Citation:
Envoyé par laurentschneider

Code:

1 2 3 select dbms_xmlgen.getxml('select tableMaitre.champ1, tableEsclave.Champ2 from tableMaitre, tableEsclave where tableEsclave.foreignkey=tableMaitre.Key') from dual;

PS: tu veux que champ2 fasse partie de champ1?

Bonjour,

Je pense qu c'est une piste mais j'ai deux question :

- est ce que c'est normal de ne pas avoir une jointure entre les deux tables emp et dept?
- comment je pourrai recupérer le résultat de cette requete?

Merci d'avance
Citation:
Envoyé par laurentschneider
que penses-tu de
Code:

1 2 3 4 5 6 7 8 9 10 11 select xmlelement( emp, xmlagg( xmlelement( dept, xmlattributes(deptno), xmlagg(sys_xmlgen(empno))))) from emp group by deptno;
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 <EMP> <DEPT DEPTNO="10"> <EMPNO>7782</EMPNO> <EMPNO>7839</EMPNO> <EMPNO>7934</EMPNO> </DEPT> <DEPT DEPTNO="20"> <EMPNO>7369</EMPNO> <EMPNO>7902</EMPNO> <EMPNO>7876</EMPNO> <EMPNO>7788</EMPNO> <EMPNO>7566</EMPNO> </DEPT> <DEPT DEPTNO="30"> <EMPNO>7499</EMPNO> <EMPNO>7698</EMPNO> <EMPNO>7654</EMPNO> <EMPNO>7844</EMPNO> <EMPNO>7900</EMPNO> <EMPNO>7521</EMPNO> </DEPT> </EMP>

je peux faire une jointure sans problème...

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select 
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename))))) 
from emp join dept using (deptno) 
group by deptno,dname;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
<EMP>
  <DEPT DNAME="ACCOUNTING" DEPTNO="10">
    <ENAME>CLARK</ENAME>
    <ENAME>KING</ENAME>
    <ENAME>MILLER</ENAME>
  </DEPT>
  <DEPT DNAME="RESEARCH" DEPTNO="20">
    <ENAME>SMITH</ENAME>
    <ENAME>FORD</ENAME>
    <ENAME>ADAMS</ENAME>
    <ENAME>SCOTT</ENAME>
    <ENAME>JONES</ENAME>
  </DEPT>
  <DEPT DNAME="SALES" DEPTNO="30">
    <ENAME>ALLEN</ENAME>
    <ENAME>BLAKE</ENAME>
    <ENAME>MARTIN</ENAME>
    <ENAME>TURNER</ENAME>
    <ENAME>JAMES</ENAME>
    <ENAME>WARD</ENAME>
  </DEPT>
</EMP>

la version d'oracle que j'utilise est la 9.0.1.0.0
lorsque je lance cette requete, je ne recois aucun résultat
y-à t-il des scripts qu'il faut les lancer?

Citation:

Envoyé par laurentschneider

je peux faire une jointure sans problème...

Code:

1
2
3
4
5
6
7
8
9
10
11
 
select 
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename))))) 
from emp join dept using (deptno) 
group by deptno,dname;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
<EMP>
  <DEPT DNAME="ACCOUNTING" DEPTNO="10">
    <ENAME>CLARK</ENAME>
    <ENAME>KING</ENAME>
    <ENAME>MILLER</ENAME>
  </DEPT>
  <DEPT DNAME="RESEARCH" DEPTNO="20">
    <ENAME>SMITH</ENAME>
    <ENAME>FORD</ENAME>
    <ENAME>ADAMS</ENAME>
    <ENAME>SCOTT</ENAME>
    <ENAME>JONES</ENAME>
  </DEPT>
  <DEPT DNAME="SALES" DEPTNO="30">
    <ENAME>ALLEN</ENAME>
    <ENAME>BLAKE</ENAME>
    <ENAME>MARTIN</ENAME>
    <ENAME>TURNER</ENAME>
    <ENAME>JAMES</ENAME>
    <ENAME>WARD</ENAME>
  </DEPT>
</EMP>

10/07/2007, 12h14
laurentschneider

non, tu as essayé dans sqlplus? aucun message d'erreur?
10/07/2007, 13h49
FABFAB125

j'ai aucun message d'erreur
voici le résultat que j'ai avec sql plus

TEST()
-----------
XMLTYPE()
XMLTYPE()

si j'utilise plsqldeveloper j'aurai aucun information et pas de message d'erreur également

Citation:

Envoyé par laurentschneider

non, tu as essayé dans sqlplus? aucun message d'erreur?

essaye d'utiliser une version de sqlplus plus récente (9.0.1, c'est le siècle passé)

sinon

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select 
  cast(
    xmlelement(
      emp,
      xmlagg(
        xmlelement(
          dept,
          xmlattributes(dname,deptno),
          xmlagg(sys_xmlgen(ename)))))
  as varchar2(4000))
from emp join dept using (deptno) 
group by deptno,dname;

Merci beaucoup, vous m'avez aidé enormement avec vos conseils.
il reste une petite question :
comment je peut selectionner en plus des informations que j'ai voulu dans la requête la ligne qui indique la version XML, exemple :

<?xml version="1.0" encoding="iso-8859-1"?>
Citation:
Envoyé par laurentschneider
essaye d'utiliser une version de sqlplus plus récente (9.0.1, c'est le siècle passé)

sinon
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select cast( xmlelement( emp, xmlagg( xmlelement( dept, xmlattributes(dname,deptno), xmlagg(sys_xmlgen(ename))))) as varchar2(4000)) from emp join dept using (deptno) group by deptno,dname;

la version
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select cast( xmlroot( xmlelement( emp, xmlagg( xmlelement( dept, xmlattributes(dname,deptno), xmlagg(sys_xmlgen(ename)))) ),version '1.0' ) as varchar2(4000)) from emp join dept using (deptno) group by deptno,dname;
note que cast as varchar2(4000), c'est moche, car ça te limite à 4000 charactères... tu ferais mieux d'employer un client qui peut afficher les xmltypes...

utilise xmlserialize plutôt que cast !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select XMLSERIALIZE(CONTENT 
xmlroot(
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename))))
     ),version '1.0'  )) 
from emp join dept using (deptno) 
group by deptno,dname;

Code:

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
 
XMLSERIALIZE(CONTENTXMLROOT(XMLELEMENT(E
----------------------------------------
<?xml version="1.0"?>
<EMP>
  <DEPT DNAME="ACCOUNTING" DEPTNO="10">
    <ENAME>CLARK</ENAME>
    <ENAME>KING</ENAME>
    <ENAME>MILLER</ENAME>
  </DEPT>
  <DEPT DNAME="RESEARCH" DEPTNO="20">
    <ENAME>SMITH</ENAME>
    <ENAME>FORD</ENAME>
    <ENAME>ADAMS</ENAME>
    <ENAME>SCOTT</ENAME>
    <ENAME>JONES</ENAME>
  </DEPT>
  <DEPT DNAME="SALES" DEPTNO="30">
    <ENAME>ALLEN</ENAME>
    <ENAME>BLAKE</ENAME>
    <ENAME>MARTIN</ENAME>
    <ENAME>TURNER</ENAME>
    <ENAME>JAMES</ENAME>
    <ENAME>WARD</ENAME>
  </DEPT>
</EMP>

J'ai appris pas mal de truc en essayant de répondre à ta question 8-)

Oui c'est bon pour ce problème.
mais ma question, comment je peut ajouter le prologue dans ma requete pour que je puisse l'affiche dans la structure XML
je voudrais afficher la partie en rouge

<?xml version="1.0" encoding="iso-8859-1"?>
<EMP>
<DEPT DNAME="ACCOUNTING" DEPTNO="10">
<ENAME>CLARK</ENAME>
<ENAME>KING</ENAME>
<ENAME>MILLER</ENAME>
</DEPT>
<DEPT DNAME="RESEARCH" DEPTNO="20">
<ENAME>SMITH</ENAME>
<ENAME>FORD</ENAME>
<ENAME>ADAMS</ENAME>
<ENAME>SCOTT</ENAME>
<ENAME>JONES</ENAME>
</DEPT>
<DEPT DNAME="SALES" DEPTNO="30">
<ENAME>ALLEN</ENAME>
<ENAME>BLAKE</ENAME>
<ENAME>MARTIN</ENAME>
<ENAME>TURNER</ENAME>
<ENAME>JAMES</ENAME>
<ENAME>WARD</ENAME>
</DEPT>
</EMP>
Citation:
Envoyé par laurentschneider
la version
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select cast( xmlroot( xmlelement( emp, xmlagg( xmlelement( dept, xmlattributes(dname,deptno), xmlagg(sys_xmlgen(ename)))) ),version '1.0' ) as varchar2(4000)) from emp join dept using (deptno) group by deptno,dname;
note que cast as varchar2(4000), c'est moche, car ça te limite à 4000 charactères... tu ferais mieux d'employer un client qui peut afficher les xmltypes...

peut-être quelque chose comme ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select XMLSERIALIZE(CONTENT 
xmltype('<?xml version="1.0" encoding="iso-8859-1"?>'||
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename) order by ename)))
     ))
     ) 
from emp join dept using (deptno) 
group by deptno,dname
order by deptno;

Vous êtes le meilleur
merci pour votre disponibilité

Citation:

Envoyé par laurentschneider

peut-être quelque chose comme ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select XMLSERIALIZE(CONTENT 
xmltype('<?xml version="1.0" encoding="iso-8859-1"?>'||
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename) order by ename)))
     ))
     ) 
from emp join dept using (deptno) 
group by deptno,dname
order by deptno;

Bonjour c'est encore moi

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<GROUPE>
    <CODE>CODE1</CODE>
    <INTITULE>Utilisateurs </INTITULE>
    <TYPE>profil</TYPE>
    <GROUPES_PARENTS>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
      <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
    </GROUPES_PARENTS>
    <UTILISATEURS>
      <UTILISATEUR>Utilisateur1</UTILISATEUR>
      <UTILISATEUR>Utilisateur2</UTILISATEUR>
      <UTILISATEUR>Utilisateur3</UTILISATEUR>
    </UTILISATEURS>
  </GROUPE>

voici la requete que ramene ce flux xml :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select xmlelement( name groupe, 
                                       xmlelement( name CODE,F.CODE),
                                       xmlelement( name intitule,F.INTITULE),                                       
                                       xmlelement( name type,F.TYPE),                                       
                                       xmlelement( name groupes_parents, 
groupe_parent,F.groupe_parent)                                                                       
                                       xmlagg(sys_xmlgen(F.groupe_parent))                                                                                                                        ),
                                       xmlelement( name utilisateurs, 
                                       xmlagg(sys_xmlgen(F.utilisateur))                                                                       
                                      )                                     
 
                                ))) as results                                                 
                         from   F

ma question est la suivnate:
dans mon flux résultat de la requête, je voudrais afficher une seule fois
<GROUPE_PARENT>PROFIL</GROUPE_PARENT> si l'information se répete plusieurs fois avec le contenu PROFIL
le résulat sohaité est :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<GROUPE>
    <CODE>CODE1</CODE>
    <INTITULE>Utilisateurs </INTITULE>
    <TYPE>profil</TYPE>
    <GROUPES_PARENTS>
     <GROUPE_PARENT>PROFIL</GROUPE_PARENT>
    </GROUPES_PARENTS>
    <UTILISATEURS>
      <UTILISATEUR>Utilisateur1</UTILISATEUR>
      <UTILISATEUR>Utilisateur2</UTILISATEUR>
      <UTILISATEUR>Utilisateur3</UTILISATEUR>
    </UTILISATEURS>
  </GROUPE>

Citation:

Envoyé par laurentschneider

peut-être quelque chose comme ça ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select XMLSERIALIZE(CONTENT 
xmltype('<?xml version="1.0" encoding="iso-8859-1"?>'||
  xmlelement(
    emp,
    xmlagg(
      xmlelement(
        dept,
        xmlattributes(dname,deptno),
        xmlagg(sys_xmlgen(ename) order by ename)))
     ))
     ) 
from emp join dept using (deptno) 
group by deptno,dname
order by deptno;