Discussion :

[Magnus]

Salut,

Je voudrais générer un fichier XML à partir de données stockées dans des tables (de base => VARCHAR2, DATE, NUMBER, etc.).

Ex :

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
 
<UNIS>
  <UNI_BASE>
    <COD_UNI>G</COD_UNI>
    <TRADS_UNI>
      <TRAD_UNI>
      <COD_LAN>FR</COD_LAN>
      <LIB>g</LIB>
      </TRAD_UNI>
      <TRAD_UNI>
      <COD_LAN>EN</COD_LAN>
      <LIB>g</LIB>
      </TRAD_UNI>
    </TRADS_UNI>
  </UNI_BASE>
  <UNI_BASE>
    <COD_UNI>KG</COD_UNI>
    <TRADS_UNI>
      <TRAD_UNI>
      <COD_LAN>FR</COD_LAN>
      <LIB>Kilogrammes</LIB>
      </TRAD_UNI>
      <TRAD_UNI>
      <COD_LAN>EN</COD_LAN>
      <LIB>kilo</LIB>
      </TRAD_UNI>
    </TRADS_UNI>
  </UNI_BASE>
</UNIS>

J'ai bien vu un élément dans la FAQ malheureusement, sur ma base, la procédure getXML n'accepte pas les paramètres montrés par Xo :

Code :

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

1
2
  FUNCTION getXML(ctx IN ctxType, metaType IN NUMBER := NONE) RETURN CLOB IS
    clb CLOB;

D'autre part, j'ai trouvé dans la doc Oracle le package DBMS_XMLGEN qui est affreusement complexe.

Quelqu'un pourrait m'expliquer comment effectuer cet export qui semble si simple ?

Merci d'avance.

Remarque : je n'ai aucune connaissance en XSL, XLST, etc. mais de solides connaissances en PL/SQL ; d'ailleurs je souhaiterais effectuer cet export en PL/SQL et non en Java ou en C comme j'ai lu dans la doc.

[Vincent Rogier]

avec une série de sql immediate et de ref cursor :

- récupération des infos du dictionnaire
- construction de la requête
- fetch du ref cursor
- a chaque itération, tu ajoute les balises..

je vois pas ou est la difficulté...

[Invité]

Je ne pense pas que notre ami cherche à contourner la difficulté, il n'a pas envi de réinventer la roue pour une fonctionnalité qui doit déjà exister.

En 10g, on avait utilisé le package xmldom - je ne sais pas si c'est dispo en 9i.

Code :

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

1
2
3
4
         obj_elmt := xmldom.createElement(doc, 'country_code');
         obj_node := xmldom.appendChild(pro_node, xmldom.makeNode(obj_elmt));
         obj_text := xmldom.createTextNode(doc, rPro.Manufacturer_tel_country_code);
         obj_node := xmldom.appendChild(obj_node, xmldom.makeNode(obj_text));

J'ai vu aussi cette article chez oracle : http://www.oracle.com/oramag/oracle/01-jul/o41xml.html

[mnitu]

Voilà une solution simple mais, qui convient rarement:

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
 
select dbms_xmlgen.getxmltype(
                       ' select dept.*'
                     ||'       ,cursor('
                     ||'                select emp.*'
                     ||'                      ,cursor('
                     ||'                               select grade'
                     ||'                               from salgrade'
                     ||'                               where emp.sal between salgrade.losal and salgrade.hisal'
                     ||'                             ) grade'
                     ||'                from emp'
                     ||'                where emp.deptno = dept.deptno'
                     ||'             ) emp_list'
                     ||' from dept'
                            ) grades
from dual
/
GRADES
--------------------------------------------
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
  <EMP_LIST>
   <EMP_LIST_ROW>
    <EMPNO>7782</EMPNO>
    <ENAME>CLARK</ENAME>
    <JOB>MANAGER</JOB>
    <MGR>7839</MGR>
    <HIREDATE>09/06/81</HIREDATE>
    <SAL>2450</SAL>
    <DEPTNO>10</DEPTNO>
    <GRADE>
     <GRADE_ROW>
      <GRADE>4</GRADE>
     </GRADE_ROW>
    </GRADE>
   </EMP_LIST_ROW>
   <EMP_LIST_ROW>
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
...

ainsi qu'un lien util.

[xdescamp]

Si la méthode simple donnée par mnitu ne convient pas, tu peux utiliser le SQLX (il me semble qu'il est disponible en 9i, mais je n'ai pas de base pour vérifier).
C'est un peu plus long à écrire, mais te permet de contrôler les balises de ton flux :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT XMLELEMENT( NAME "LISTE",
                   XMLAGG( XMLELEMENT (NAME "EMPLOYE",
                                       XMLFOREST (empno AS "NUMERO",
                                                  ename AS "NOM",
                                                  job AS "JOB")
                                       )
                          )
                  ).getClobVal()
FROM scott.emp;

Cela te retourne un CLOB.

[Magnus]

Bonjour à tous,

J'apprécie la variété et la quantité de vos réponses.

Concernant vos questions, je ne cherche évidemment pas à réinventer la roue mais bien à savoir gérer des fichiers XML en entrée et en sortie, c'est-à-dire respectivement l'équivalent d'un loader qui saurait lire un fichier XML et le charger dans une voire plusieurs tables et générer un fichier XML avec mes propres balises FACILEMENT à partir de données dans des tables on ne peut plus classiques.

Par conséquent, voici quelques remarques :

mnitu >> ok mais comment supprimer ces balises automatiques ROWSET, ROW, EMP_LIST_ROW ?

xdescamp >> ça me plait bien, je vais creuser cette solution de mon côté

jerome_Mtl >> le package xmldom existe en 9i donc je vais aussi étudier ta proposition

Vincent_Rogier >> j'ai déjà implémenté avec succès cet algorithme qui présente l'inconvénient de comporter une fonction assez ignoble pour la génération du fichier XML. En effet, il me faut gérer un grand nombre de curseurs (1 pour chaque niveau de l'arborescence à peu près) mais surtout je suis incapable d'intégrer un tel fichier et je me disais qu'Oracle dispose nécessairement d'un mécanisme de reconnaissance du XML pour intégrer un fichier XML dans 1 voire plusieurs tables (ce que je comparais plus haut à un loader).

[mnitu]

...mnitu >> ok mais comment supprimer ces balises automatiques ROWSET, ROW, EMP_LIST_ROW ?
...

Tu prends le contrôle avec XMLELEMENT, XMLAGG, XMLATTRIBUTE etc. comme Xdescamp le montre. J’ai un petit tutoriel en pdf écrit par Ken Atkins (XML Data Into and Out of Oracle– Using PL/SQL, 542K) pour lequel je n’arrive malheureusement plus à retrouver l’adresse de téléchargement, qui pourrait t’intéresser. Tu passe 10 minutes avec et t’auras une bonne idée par quoi commencer.

[xdescamp]

J’ai un petit tutoriel en pdf écrit par Ken Atkins (XML Data Into and Out of Oracle– Using PL/SQL, 542K) pour lequel je n’arrive malheureusement plus à retrouver l’adresse de téléchargement, qui pourrait t’intéresser.

Il s'agissait d'un présentation faite durant OPP 2007 qui n'est malheureusement plus disponible. J'ai cependant trouvé un site qui en héberge une copie http://www.cedet.dk/docs/2read/Atkins_Ken1.pdf ainsi qu'une autre présentation du même auteur sur la manipulation des XML http://www.cedet.dk/docs/2read/Atkins_Ken2.pdf.

Merci mnitu pour la référence, cette doc a vraiment l'air claire.

[mnitu]

Il s'agissait d'un présentation faite durant OPP 2007 qui n'est malheureusement plus disponible. ...

Oui, c'est bien ça. Je viens de trouver aussi le blog du Marco Gralike que je trouve excellent.
Et merci pour la suite, le fichier Atkins_Ken2, je l'avais raté.

[Magnus]

Le mieux que j'arrive à produire pour l'instant ressemble à ça :

Code :

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

1
2
3
4
SELECT XMLELEMENT (NAME "UNIS",
                   XMLAGG (xmlelement (name "COD_UNI", xum.uom_code ,  XMLFOREST (xtl.language AS cod_lan, xtl.description AS lib) )))
  FROM xum, xtl
 WHERE xum.session_id = xtl.session_id AND xtl.cod_art_um = xum.uom_code

produit :

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
 
<UNIS>
  <COD_UNI>KG    <COD_LAN>F</COD_LAN>
    <LIB>FR - Kilogramme</LIB>
</COD_UNI>
  <COD_UNI>KG    <COD_LAN>US</COD_LAN>
    <LIB>US - Kilogramme</LIB>
</COD_UNI>
  <COD_UNI>UN    <COD_LAN>F</COD_LAN>
    <LIB>Unité</LIB>
</COD_UNI>
  <COD_UNI>UN    <COD_LAN>US</COD_LAN>
    <LIB>Unité</LIB>
</COD_UNI>
</UNIS>

Or, je voudrais que la balise fermante </COD_UNI> soit fermée à une "fréquence" inférieure mais j'ai l'impression que la demande de mon client "n'est pas hiérarchisée comme il le faut" et donc que le package standard d'Oracle ne peut pas le gérer :

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
<UNIS>
<UNI_BASE>
  <COD_UNI>KG</COD_UNI>
  <TRADS_UNI>
    <TRAD_UNI>
    <COD_LAN>F</COD_LAN>
    <LIB>FR - Kilogramme</LIB>
    </TRAD_UNI>
    <TRAD_UNI>
    <COD_LAN>US</COD_LAN>
    <LIB>US - Kilogramme</LIB>
    </TRAD_UNI>
</TRADS_UNI>
</UNI_BASE>
<UNI_BASE>
  <COD_UNI>UN</COD_UNI>
  <TRADS_UNI>
  <TRAD_UNI>
    <COD_LAN>F</COD_LAN>
    <LIB>Unité</LIB>
  </TRAD_UNI>
  <TRAD_UNI>
    <COD_LAN>US</COD_LAN>
    <LIB>Unité</LIB>
  </TRAD_UNI>
</TRADS_UNI>
</UNI_BASE>
</UNIS>

Je me trompe ?
C'est faisable selon vous (je ne demande pas nécessairement la requête qui va bien).

[xdescamp]

Ca doit être faisable.
Je pense qu'il faudrait que dans ton XMLELEMENT "UNIS" tu fasses 2 XMLAGG (si c'est possible syntaxiquement), ou qq chose du style mettre un XMLFOREST qui contiendrait les 2 XMLAG.

[Magnus]

Je progresse :

Code :

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

1
2
3
4
5
6
7
8
SELECT XMLELEMENT (
          NAME "UNIS",
          XMLELEMENT (NAME "UNI_BASE",
                      XMLELEMENT ("COD_UNI", xum.uom_code),
                      XMLELEMENT ("TRADS_UNI", XMLELEMENT ("TRAD_UNI", XMLFOREST (xtl.language, xtl.description))))
       )
  FROM xxin_s_782_alis_um xum, mtl_units_of_measure_tl xtl
 WHERE xum.uom_code = xtl.uom_code

produit 4 enregistrements (2 pour chaque unité KG / UN et 2 pour chaque FR / US) ; ex :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
<UNIS>
  <UNI_BASE>
    <COD_UNI>KG</COD_UNI>
    <TRADS_UNI>
      <TRAD_UNI>
        <LANGUAGE>US</LANGUAGE>
        <DESCRIPTION>US - Kilogramme</DESCRIPTION>
      </TRAD_UNI>
    </TRADS_UNI>
  </UNI_BASE>
</UNIS>

Il faudrait donc que, pour chaque unité, j'ai les 2 traductions dans le node TRADS_UNI.
Or, si je tente d'ajouter un XMLAGG par ex :

Code :

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

1
2
3
4
5
6
7
8
SELECT XMLELEMENT (
          NAME "UNIS",
          XMLELEMENT (NAME "UNI_BASE",
                      XMLELEMENT ("COD_UNI", xum.uom_code),
                      XMLELEMENT ("TRADS_UNI", xmlagg ( XMLELEMENT ("TRAD_UNI", XMLFOREST (xtl.language, xtl.description)))))
       )
  FROM xxin_s_782_alis_um xum, mtl_units_of_measure_tl xtl
 WHERE xum.uom_code = xtl.uom_code

j'obtiens l'erreur suivante :

ORA-00937: not a single-group group function

Idem avec XMLFOREST.
Je pense que je ne saisis pas bien la subtilité de ces 2 fonctions.

[xdescamp]

Le XMLAGG est une fonction d'aggrégat (comme SUM par ex.). Il faut donc que tu rajoutes un GROUP BY sur les valeurs parentes.
Je n'ai pas essayé avec plus de 2 niveaux, donc je ne sais pas ce qu'il faut faire si tu en as plus.

Une autre solution est d'utiliser une sous-requête pour chaque niveau, mais je ne sais pas ce que cela va donner en performances.
Pour cela, il faut que tu enlève mtl_units_of_measure_tl et que tu remplaces ton XMLAGG par un (SELECT XMLAGG(...) FROM mtl_system_items_b WHERE ...)

[Magnus]

J'ai finalement quasi terminé la génération de mon fichier XML via le package DBMS_XMLGEN et les MULTISET.
Ce package me sert à insérer le contenu de ma requête dans une colonne de type CLOB d'une table de travail.
Enfin, je lis simplement le contenu de cette table via un spool pour constituer mon fichier XML.

Cependant, il y a un élément sur lequel je n'arrive pas à prendre la main et que je ne trouve pas dans la doc Oracle.
Commet modifier l'entête du fichier XML généré qui est systématiquement :

<?xml version="1.0"?>

En effet, je voudrais par exemple remplacer ou compléter cet entête pour qu'il soit celui-ci :

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

J'ai bien trouvé un moyen en effectuant un update de mon CLOB inséré dans ma table temporaire via DBMS_XMLGEN sur toute chaîne commençant par '<?xml version%'.
Malheureusement cette solution est immonde et je suis étonné qu'Oracle propose de compléter cet entête en standard.

J'offre une (virtua-)binouze à celui/celle qui me file cette procédure standard qui permet de faire ça.

[Magnus]

Pour info, il existe le package

Code :

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

sys.dbms_xmlquery

qui possède une procédure

Code :

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

setEncodingTag

permettant d'ajouter l'info d'encodage dans la ligne d'entête d'un fichier XML.
Ce package possède toutes les procédures du package

Code :

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

(apps.)dbms_xmlgen

+ d'autres bien utiles dont celle-ci.

Merci de votre aide.
M.