Oracle/XML avec une requete imbriquée

**FABFAB125** · 09/07/2007, 11h30

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

**laurentschneider** · 09/07/2007, 13h54

Code :

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

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?

**laurentschneider** · 09/07/2007, 14h10

que penses-tu de

Code :

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

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 :

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

**FABFAB125** · 10/07/2007, 10h16

Bonjour,
mais cette commande donne la structure suivante :

ROWSET>
<ROW num="1">
<champ1>donneechamp1</champ1>
<Champ2>donneechamp2</Champ2>
</ROW>
</ROWSET>

Envoyé par laurentschneider

Code :

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

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?

**FABFAB125** · 10/07/2007, 10h25

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

Envoyé par laurentschneider

que penses-tu de

Code :

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

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 :

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

**laurentschneider** · 10/07/2007, 10h54

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

Code :

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

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 :

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

**FABFAB125** · 10/07/2007, 11h56

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?

Envoyé par laurentschneider

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

Code :

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

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 :

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

**laurentschneider** · 10/07/2007, 12h14

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

**FABFAB125** · 10/07/2007, 13h49

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

Envoyé par laurentschneider

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

**laurentschneider** · 10/07/2007, 16h24

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

sinon

Code :

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

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;

**FABFAB125** · 11/07/2007, 11h39

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"?>

Envoyé par laurentschneider

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

sinon

Code :

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

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;

**laurentschneider** · 11/07/2007, 12h54

la version

Code :

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

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...

**laurentschneider** · 11/07/2007, 12h58

utilise xmlserialize plutôt que cast !

Code :

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

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 :

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

**FABFAB125** · 11/07/2007, 13h01

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>

Envoyé par laurentschneider

la version

Code :

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

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...

**laurentschneider** · 11/07/2007, 13h38

peut-être quelque chose comme ça ?

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
 
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;

**FABFAB125** · 11/07/2007, 14h13

Vous êtes le meilleur
merci pour votre disponibilité

Envoyé par laurentschneider

peut-être quelque chose comme ça ?

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
 
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;

**FABFAB125** · 20/07/2007, 12h52

Bonjour c'est encore moi

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
 
<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 :

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

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 :

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

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>

Envoyé par laurentschneider

peut-être quelque chose comme ça ?

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
 
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;

Oracle/XML avec une requete imbriquée

SQL Oracle

Discussions similaires

Partager

Partager