Intégration XML à DB2.

La version 7.1 de IBM i apporte à SQL l'intégration du langage XML.

Cette intégration a été normalisée sous le nom de SQLXML, elle propose :

• Champs de type XML avec possibilité de validation d'un schéma XSD lors de l'insertion
• Fonctions pour produire du XML à partir de données élémentaires
• Sérialisation du XML en types simples (CHAR, BLOB, ...)
• Transformation du XML avec XSLT
• possibilité d'import/export avec des annotations XSD.
  
  
• dans le même temps le produit OmniFind (5733OMF) est livré en V1R2 et permet l'indexation et la recherche de champs XML.

Le type XML

commençons par la création d'une table avec le type de champs XML, tel que donné par la documentation

CREATE SCHEMA POSAMPLE;

SET CURRENT SCHEMA POSAMPLE;

CREATE TABLE CUSTOMER ( 
               CID BIGINT NOT NULL PRIMARY KEY , 
               INFO XML ) ;

Les champs de type XML peuvent faire jusqu'à 2 Go, la totalité d'une ligne ne peut pas dépasser 3,5 Go.

Ils sont stockés dans le CCSID indiqué par SQL_XML_DATA_CCSID dans QAQQINI, UTF-8 (1208) par défaut.

Puis insertion de données, le parsing (la conversion CHAR -> XML) est automatique lors des insertions,
mais vous pouvez parser de manière explicite avec XMLPARSE() pour utiliser des options (voir cette fonction)

INSERT INTO Customer (Cid, Info) VALUES (1000,        
'<customerinfo xmlns="http://posample.org" Cid="1000">
  <name>Kathy Smith</name>                            
  <addr country="Canada">                             
    <street>5 Rosewood</street>                       
    <city>Toronto</city>                              
    <prov-state>Ontario</prov-state>                  
    <pcode-zip>M6W 1E6</pcode-zip>                    
  </addr>                                             
  <phone type="work">416-555-1358</phone>             
</customerinfo>')                                    
 
INSERT INTO Customer (Cid, Info) VALUES (1002,           
'<customerinfo xmlns="http://posample.org" Cid="1002">   
 <name>Jim Noodle</name>                           
 <addr country="Canada">                               
  <street>25 EastCreek</street>                        
  <city>Markham</city>                               
  <prov-state>Ontario</prov-state>                   
  <pcode-zip>N9C 3T6</pcode-zip>                     
  </addr>                                              
 <phone type="work">905-555-7258</phone>               
</customerinfo>')                                       

INSERT INTO Customer (Cid, Info) VALUES (1003,                  
'<customerinfo xmlns="http://posample.org" Cid="1003">          
<name>Robert Shoemaker</name>                                   
<addr country="Canada">                                         
<street>1596 Baseline</street>                                  
<city>Aurora</city>                                             
<prov-state>Ontario</prov-state>                                
<pcode-zip>N8X 7F8</pcode-zip>                                  
</addr>                                                         
<phone type="work">905-555-2937</phone>                         
</customerinfo>')

Mais aussi (voir la fonction GET_XML_FILE ici):

INSERT INTO POSAMPLE/CUSTOMER   
 VALUES( 1004 , GET_XML_FILE('/temp/client04.xml') )

le document doit être bien formé, sinon vous recevrez SQ20398

                     Complément d'informations sur message                     
                                                                               
 ID message . . . . . . :   SQ20398                                            
                                                                               
                                                                               
 Message . . . . :   Echec de l'analyse syntaxique XML.                        
                                                                               
 Cause . . . . . :   L'analyse syntaxique XML a échoué pendant le traitement   
   SQL. Le décalage en octets dans la valeur XML en cours de traitement après  
   conversion en UTF-8 est de 203. La description de l'erreur de l'analyseur   
   syntaxique XML est la suivante : Element type "addr" must be followed by    
   either attribute specifications, ">" or "/>".                               
 Que faire . . . :   Corrigez la valeur XML. Renouvelez ensuite votre demande. 
                                                                               
    

Voyons le résultat

   Attention : Client Access V7 impératif !

Sous une session 5250, la sérialisation (conversion en une chaîne simple) n'est pas faite par défaut :

il faut utiliser XMLSERIALIZE(INFO as CHAR(2000) ) pour voir le contenu, le CCSID du job doit être renseigné (pas de 65535).

La zone INFO doit être manipulée dans sa totalité, il n'est pas possible de n'afficher ou de ne modifier que la ville du flux xml précédent.

 par exemple

update customer set info =                               
'<customerinfo xmlns="http://posample.org" Cid="1002">   
 <name>Jim Noodle</name>                                 
 <addr country="Canada">                                 
  <street>1150 Maple Drive</street>                      
  <city>Newtown</city>ity>                               
  <prov-state>Ontario</prov-state>                       
  <pcode-zip>Z9Z 2P2</pcode-zip>                         
  </addr>r>                                              
 <phone type="work">905-555-7258</phone>                 
</customerinfo>'                                         
 where cid = 1002                                        

Vous pouvez aussi utiliser les nouvelles fonctions SQL, qui ne fonctionnent QUE sous commitment contrôle

GET_BLOB_FROM_FILE(chemin , option)	retourne un BLOB LOCATOR, sans conversion du CCSID
GET_CLOB_FROM_FILE(chemin , option)	retourne un CLOB LOCATOR dans le CCSID du job
GET_DBCLOB_FROM_FILE(chemin , option)	retourne un DBCLOB LOCATOR dans le CCSID DBCS par défaut
GET_XML_FILE(chemin)	retourne un BLOB LOCATOR en UTF-8, si ce dernier ne possède pas de déclaration XML la fonction l'ajoute.

pour la notion de locator, voir ci dessous

Le chemin peut être :

• les coordonnées d'un fichier dans l'IFS, répertoire inclus.
• un membre source sous la forme 'BIBILOTHEQUE/FICHIER(MEMBRE)'

Par exemple

insert into posample/customer               
 values(1010, get_xml_file('/clients/volubis.xml') )

Option, qui est facultatif, peut contenir :

• 0, le fichier est chargé tel que.
• 1, le fichier est chargé après élimination des espaces de fin de fichier, sauf le premier qui est conservé.

 

les mêmes fonctions, peuvent, du coup, être utilisées directement en RPG pour lire un fichier IFS

Dvariable S A len(20000) varying D /free EXEC SQL values cast(GET_CLOB_FROM_FILE('/annotate.log') as varchar(20000) ) into :variable; /end-free

 

---

Comment sinon, manipuler du XML en programmation (RPGLE et COBOL particulièrement)

 

Il faut pour cela revoir la manipulation des BLOB et CLOB tel que vue en V4R40.

 > Une colonne de type LOB peut-être manipulée par


    -son contenu (si votre langage supporte des variables aussi grandes)

       vous devez déclarer en RPG par:


     DMYBLOB           S                   SQLTYPE(BLOB:500)

       ce qui génère :

     D MYBLOB          DS
     D MYBLOB_LEN                    10U 0
     D MYBLOB_DATA                  500A

 Dvariable         S                    SQLTYPE(CLOB : 20000)
 D
  /free
    EXEC SQL
      values GET_CLOB_FROM_FILE('/annotate.log') into :variable;
       // manipuler ensuite variable_len et variable_data
  /end-free                                        

    -vous pouvez utiliser un identifiant appelé "LOCATOR", qui permet :


                                       + de ne pas transférer les données
                                         dans le programme.

                                       + de faire des copies de fichiers ...


       vous devez déclarer en RPG par:

     D MYCLOB          S                   SQLTYPE(CLOB_LOCATOR)

       ce qui génère :

     D MYCLOB          S             10U 0


       vous pouvez utiliser l'identifiant en lieu et place de la colonne
         par les nouvelles instructions SET et VALUE.


    C/EXEC SQL
    C+   VALUE POSSTR(:MYCLOB, 'formation') INTO :debut
    C/END-EXEC

 -vous pouvez utiliser un fichier appelé "FILE LOCATOR", qui permet :


                                       + de copier le contenu d'un LOB
                                          dans un fichier IFS


                                       + de renseigner un LOB à partir
                                          du contenu d'un fichier IFS.


       vous devez déclarer en RPG par:


     D MYFILE          S                   SQLTYPE(CLOB_FILE)


       ce qui génère :


     D MYFILE          DS
     D  MYFILE_NL                    10U 0        [lg du nom]
     D  MYFILE_DL                    10U 0        [lg des Data]
     D  MYFILE_FO                    10U 0        [file permission]
      * SQL génère SQFRD (2), SQFCRT (8), SQFOVR(16) et SQFAPP(32)
     D  MYFILE_NAME                 255A          [nom du fichier]

Exemple en COBOL


 en Working Storage Section :

     01 rapport USAGE IS SQL   TYPE IS CLOB-FILE


 puis en traitement

       move "RAPPORT.TXT"      to rapport-NAME.
       move  11                to rapport-LENGTH.
       move SQL-FILE-OVERWRITE to rapport-FILE-OPTIONS.


        EXEC SQL SELECT rapport INTO :rapport
                        FROM  ...
                        WHERE ...
         END-EXEC.


 ce traitement place copie du contenu de la colonne "rapport" dans le
     fichier "RAPPORT.TXT" qui est un fichier IFS.
 un ordre INSERT aurait renseigné la colonne par copie du fichier.

 

LE XML se manipule de la même manière avec les types suivants

• XML_BLOB
• XML_CLOB
• XML_DBCLOB (à privilégier, pour rappel, le XML est par défaut stocké en CCSID 1208)
  
  La déclaration suivante

  D MON_XML            S               SQLTYPE(XML_DBCLOB:2500)

  Génère :

  D MON_XML            DS
  D MON_XML_LEN                 10U
  D MON_XML_DATA                  C    LEN(2500)  
  
        

• XML_LOCATOR
  
  
  
• XML_BLOB_FILE
• XML_CLOB_FILE
• XML_DBCLOB_FILE
  
  La déclaration suivante

  D MON_FICHIERXML     S               SQLTYPE(XML_CLOB_FILE)

  Génère :

  D MON_FICHIERXML     DS
  D MON_FICHIERXML_NL           10U
  D MON_FICHIERXML_DL           10U
  D MON_FICHIERXML_FO           10U
  D MON_FICHIERXML_NAME        255A

Exemples en RPG (rappel, par défaut les champs XML sont en UNICODE)

  * récupération du XML dans une variable du langage
  *=================================================
 D MON_XML          S                   SQLTYPE(XML_DBCLOB:2500)
 D pos              S              5i 0
  /free
       exec sql
          select info into :mon_xml from posample/customer
            where cid = 1000;
       eval pos = %scan(%ucs2('phone') :mon_xml_data); //pos = 260
       *inlr = *on;
  /end-free  


  * récupération du XML dans un fichier de l'IFS
  *==============================================
 DMON_FICHIERXML   S                   SQLTYPE(XML_DBCLOB_FILE)
  /free
    mon_fichierxml_name = '/temp/xml01.xml';
    mon_fichierxml_nl = %len(%trim(mon_fichierxml_name));
    mon_fichierxml_fo = SQFOVR;

     exec sql
       select info into :mon_fichierxml from posample/customer
         where cid = 1000;
     *inlr = *on;
  /end-free 

le fichier xml01.xml est bien généré dans /temp (CCSID 1208) et contient :

Browse : /temp/xml01.xml 
Record :       1   of       3 by  18                      Column :    1    132 by 131 
Control : 

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....3. 
************Beginning of data************** 
<?xml version="1.0" encoding="UTF-8"?><customerinfo xmlns="http://posample.org" Cid="1000"><name>Kathy Smith</name><addr country="Ca
nada"><street>5 Rosewood</street><city>Toronto</city><prov-state>Ontario</prov-state><pcode-zip>M6W 1E6</pcode-zip></addr><phone typ
e="work">416-555-1358</phone></customerinfo> 
************End of Data******************** 

un ordre INSERT aurait lu le fichier XML de l'IFS et placé son contenu dans une colonne de la table SQL.

---

 

Les fonctions ligne à ligne (scalaires) permettant de produire du XML :

(tous les exemples sont lancés depuis iSeries navigator, sous 5250 il faudrait utiliser XMLSERIALIZE):

• XMLDOCUMENT : production d'un flux XML à partir d'une chaîne de caractère (validation comprise).
  Cette action est implicite lors des ordres INSERT et UPDATE, comme vu plus haut.
  
  
• XMLPARSE : production après vérification, d'un flux XML, avec choix de conservation des espaces ou non
  

--STRIP WHITESPACE--- XMLPARSE(DOCUMENT '<xml> ... </xml>' | | -PRESERVE WHITESPACE-

• XMLVALIDATE : validation d'un flux XML à l'aide d'un schéma XSD enregistré dans XSROBJECTS (voir ci-dessous)
  

XMLVALIDATE(DOCUMENT '<xml> ... </xml>' ACCORDING TO XMLSCHEMA --> |---ID un-schéma-enregistré--| >---| |---- |--une URI valide------------|





• XMLTRANSFORM : transforme un flux XML à l'aide de XSLT
  

XMLTRANSFORM( flux-xml USING 'source-XSLT')





• XMLTEXT : production d'un texte compatible XML
  

select XMLTEXT('100 est > à 99 & à 98') FROM SYSIBM.SYSDUMMY1 ; 100 est > à 99 & à 98





• XMLELEMENT : production d'un élément XML
  
  

select XMLELEMENT(name "region" , region) from bdvin1.regions; <region>Abruzzo </region> <region>Ahr </region> <region>Alsace </region> <region>Andalucía </region> <region>Aragón </region>



Cette fonction peut être imbriquée


select xmlelement(name "Appellations", xmlelement(name "appellation" , appellation), xmlelement(name "region" , region_code) ) from bdvin1.appellations ; <Appellations><appellation>Alella D.O. </appellation><region>21</region></Appellations> <Appellations><appellation>Ampurdàn- costa brava D.O </appellation><region>21</region></Appellations> <Appellations><appellation>Anoia </appellation><region>21</region></Appellations> <Appellations><appellation>Bajo ebro-montsià </appellation><region>21</region></Appellations> <Appellations><appellation>Conca de barberà D.O </appellation><region>21</region></Appellations> <Appellations><appellation>Conca de tremp </appellation><region>21</region></Appellations> <Appellations><appellation>Costers del segre D.O </appellation><region>21</region></Appellations>




• XMLATTRIBUTES : production d'un attribut, uniquement valide dans XMLELEMENT
  

select xmlelement(name "Appellations", XMLATTRIBUTES(pays_code as "pays"), appellation , region_code ) from bdvin1.appellations join bdvin1.regions using (region_code) ; <Appellations pays="11">Alella D.O. 21</Appellations> <Appellations pays="11">Ampurdàn- costa brava D.O 21</Appellations> <Appellations pays="11">Anoia 21</Appellations> <Appellations pays="11">Bajo ebro-montsià 21</Appellations> select xmlelement(name "Appellations", XMLATTRIBUTES(pays_code as "pays"), xmlelement(name "appellation" , appellation), xmlelement(name "region" , region_code) ) from bdvin1.appellations join bdvin1.regions using (region_code) ; <Appellations pays="11"><appellation>Alella D.O. </appellation><region>21</region></Appellations> <Appellations pays="11"><appellation>Ampurdàn- costa brava D.O </appellation><region>21</region></Appellations> <Appellations pays="11"><appellation>Anoia </appellation><region>21</region></Appellations> <Appellations pays="11"><appellation>Bajo ebro-montsià </appellation><region>21</region></Appellations>




• XMLNAMESPACES , production d'un balise d'espace de nommage (namespace)
  

select xmlelement(name "vin:Appellations", XMLNAMESPACES('http://www.volubis.fr/vins/1.0' AS "vin") , appellation , region_code ) from bdvin1.appellations join bdvin1.regions using (region_code) ; <vin:Appellations xmlns:vin="http://www.volubis.fr/vins/1.0"> Alella D.O. 21 </vin:Appellations>




• XMLPI , production d'une balise processing instruction
  

SELECT XMLPI(NAME "Instruction", 'APPUYEZ SUR ENTREE')     FROM SYSIBM.SYSDUMMY1 <?Instruction APPUYEZ SUR ENTREE?>




• XMLCOMMENT, production d'un commentaire XML
  

select XMLCOMMENT('A consommer avec modération') FROM SYSIBM.SYSDUMMY1 ; <!--A consommer avec modération-->




• XMLCONCAT, production d'un flux XML à partir de la concaténation de deux.
  

select XMLCONCAT(XMLELEMENT(name "region" , region) , XMLELEMENT(name "pays" , pays_code)) from bdvin1.regions; ; <region>Abruzzo </region><pays>18</pays> <region>Ahr </region><pays>2</pays> <region>Alsace </region><pays>12</pays> <region>Andalucía </region><pays>11</pays>





• XMLFOREST, production d'une suite d'éléments XML à partir des colonnes d'une table
  

select XMLFOREST(region , pays_code) from bdvin1.regions; <REGION>Abruzzo </REGION><PAYS_CODE>18</PAYS_CODE> <REGION>Ahr </REGION><PAYS_CODE>2</PAYS_CODE> <REGION>Alsace </REGION><PAYS_CODE>12</PAYS_CODE> <REGION>Andalucía </REGION><PAYS_CODE>11</PAYS_CODE> select XMLFOREST(region AS "region" , pays_code as "pays") from bdvin1.regions; <region>Abruzzo </region><pays>18</pays> <region>Ahr </region><pays>2</pays> <region>Alsace </region><pays>12</pays> <region>Andalucía </region><pays>11</pays>

• XMLROW, production d'une ligne XML à partir des colonnes d'une table
  

  select XMLROW(appellation, region_code) from bdvin1.appellations; <row><APPELLATION>Alella D.O. </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Ampurdàn- costa brava D.O </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Anoia </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Bajo ebro-montsià </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Conca de barberà D.O </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Conca de tremp </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Costers del segre D.O </APPELLATION><REGION_CODE>21</REGION_CODE></row> <row><APPELLATION>Penedés D.O </APPELLATION><REGION_CODE>21</REGION_CODE></row> select XMLROW(appellation, region_code OPTION ROW "une_appellation") from bdvin1.appellations; <une_appellation><APPELLATION>Alella D.O. </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Ampurdàn- costa brava D.O </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Anoia </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Bajo ebro-montsià </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Conca de barberà D.O </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Conca de tremp </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Costers del segre D.O </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation> <une_appellation><APPELLATION>Penedés D.O </APPELLATION><REGION_CODE>21</REGION_CODE></une_appellation>

Fonctions de groupe (agrégat)

• XMLAGG, production d'une série d'élements XML par groupe de données
  

select pays_code, xmlagg(XMLELEMENT(name "region" , region)) from bdvin1.regions group by pays_code ; 1 <region>Constantia </region><region>Durbanville </region> ... 2 <region>Ahr </region><region>Baden </region> ... 5 <region>Burgenland Neusiedlersee </region><region>Niederösterreich </region> ... 11 <region>Andalucía </region><region>Aragón </region> ... 12 <region>Alsace </region><region>Bordeaux </region> ... 15 <region>Tokaji </region> 18 <region>Abruzzo </region><region>Basilicata </region> ... 25 <region>Douro </region> 30 <region>Berne </region><region>Fribourg </region> ... 33 <region>Arizona </region><region>Arkansas </region> ... - <region>Franschhoek </region><region>Hermanus-Walker Bay </region> ...

• XMLGROUP , production d'un flux XML valide, par groupe de données
  

select pays_code, XMLGROUP(appellation, region ORDER BY region) from bdvin1.regions join bdvin1.appellations using(region_code) group by pays_code; 11 <rowset><row><APPELLATION>Aljarafe </APPELLATION><REGION>Andalucía</REGION></row><row><APPELLATION>Bailen ... </rowset> 12 <rowset><row><APPELLATION>Alsace </APPELLATION><REGION>Alsace </REGION></row><row><APPELLATION>Alsace chasselas ... </rowset> 18 <rowset><row><APPELLATION>Montepulciano</APPELLATION><REGION>Abruzo </REGION></row><row><APPELLATION>Brunelo... </rowset> select pays_code, XMLGROUP(appellation, region ORDER BY region OPTION row "appellations" root "regions" ) from bdvin1.regions join bdvin1.appellations using(region_code) group by pays_code; 11 <regions><appellations><APPELLATION>Aljarafe </APPELLATION><REGION>Andalucía</REGION></appellations><appellations><APPELLATION>Bailen ... </regions> 12 <regions><appellations><APPELLATION>Alsace </APPELLATION><REGION>Alsace </REGION></appellations><appellations><APPELLATION>Alsace chasselas ... </regions> 18 <regions><appellations><APPELLATION>Montepulciano</APPELLATION><REGION>Abruzo </REGION></appellations><appellations><APPELLATION>Brunelo... </rregions>

 

---

XSD, validation par un schéma XML

Il faut commencer par enregistrer le schéma dans XSROBJECTS de QSYS2 par la procédure XRS_REGISTER

Enregistrons le schéma XML suivant (nous allons passer par une procédure, XSR_REGISTER attendant un paramètre de type BLOB)

CREATE PROCEDURE SAMPLE_REGISTER  
LANGUAGE SQL  
BEGIN
   DECLARE CONTENT BLOB(1M); 
   VALUES BLOB('<?xml version="1.0"?>
  <xs:schema targetNamespace="http://posample.org"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
    <xs:element name="customerinfo">
      <xs:complexType>
        <xs:sequence>
          <xs:element name="name" type="xs:string" minOccurs="1" />
          <xs:element name="addr" minOccurs="1" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="street" type="xs:string" minOccurs="1" />
                <xs:element name="city" type="xs:string" minOccurs="1" />
                <xs:element name="prov-state" type="xs:string" minOccurs="1" />
                <xs:element name="pcode-zip" type="xs:string" minOccurs="1" />
              </xs:sequence>
              <xs:attribute name="country" type="xs:string" />
            </xs:complexType>
          </xs:element>
          <xs:element name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded">
            <xs:complexType>
              <xs:simpleContent>
                <xs:extension base="xs:string">
                  <xs:attribute name="type" form="unqualified" type="xs:string" />
                </xs:extension>
              </xs:simpleContent>
            </xs:complexType>
          </xs:element>
          <xs:element name="assistant" minOccurs="0" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="name" type="xs:string" minOccurs="0" />
                <xs:element name="phone" nillable="true" minOccurs="0" maxOccurs="unbounded">
                  <xs:complexType>
                    <xs:simpleContent  >
                      <xs:extension base="xs:string">
                        <xs:attribute name="type" type="xs:string" />
                      </xs:extension>
                    </xs:simpleContent>
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
        <xs:attribute name="Cid" type="xs:integer" />
      </xs:complexType>
    </xs:element>
  </xs:schema>')
 INTO CONTENT; 

     CALL SYSPROC.XSR_REGISTER('POSAMPLE', 'CUSTOMER', 'http://posample.org', CONTENT, null);
END;  

 mais nous aurions pu aussi saisir le schéma XSD dans l'IFS et utiliser GET_XML_FILE :

CALL SYSPROC.XSR_REGISTER('POSAMPLE', 'CUSTOMER', 'http://posample.org', GET_XML_FILE('schema.xsd'), null);
   voire même aller chercher le schéma sur le net, avec les nouvelles fonctions de SYSTOOLS.

Exécutez et validez par :

CALL POSAMPLE.SAMPLE_REGISTER;

CALL SYSPROC.XSR_COMPLETE('POSAMPLE', 'CUSTOMER', null, 0);

Vérifiez en affichant le contenu de XSROBJECTS

Essayons d'insérer une donnée ne respectant pas les règles.
-> ici nous ne fournissons pas d'élément <addr> alors que ce dernier est déclaré obligatoire (<xs:element name="addr" minOccurs="1")

INSERT INTO POSAMPLE.Customer(Cid, Info) VALUES (1004, 
XMLVALIDATE (XMLPARSE (DOCUMENT 
         '<customerinfo xmlns="http://posample.org" Cid="1003"> 
           <name>Robert Shoemaker</name> 
           <phone type="work">905-555-7258</phone> 
           <phone type="home">416-555-2937</phone> 
           <phone type="cell">905-555-8743</phone> 
           <phone type="cottage">613-555-3278</phone> 
         </customerinfo>' PRESERVE WHITESPACE )
  ACCORDING TO XMLSCHEMA ID posample.customer ));

--------------------------------------------------------------------------------------------------
Etat SQL : 2201R
Code fournisseur : -20399
Message : [SQ20399] Echec de l'analyse syntaxique ou de la validation XML.
 Cause . . . . . :   L'analyse syntaxique XML a échoué pendant la validation. 
Le décalage en octets dans la valeur XML en cours de traitement après conversion en UTF-8 est de 109.
La description de l'erreur de l'analyseur syntaxique XML est la suivante : cvc-complex-type.2.4.a: 
Expecting element with local name "addr" but saw "phone".
 Que faire . . . :   Corrigez l'incident lié au document de l'instance XML.
 Relancez XMLVALIDATE ou XDBDECOMPXML.

par contre

INSERT INTO posample.Customer(Cid, Info) VALUES (1004, XMLVALIDATE (XMLPARSE (DOCUMENT 
'<customerinfo xmlns="http://posample.org" Cid="1003"> 
  <name>Robert Shoemaker</name> 
  <addr country="Canada"> 
    <street>1596 Baseline</street> 
    <city>Aurora</city> 
    <prov-state>Ontario</prov-state> 
    <pcode-zip>N8X 7F8</pcode-zip> 
  </addr> 
  <phone type="work">905-555-7258</phone> 
  <phone type="home">416-555-2937</phone> 
  <phone type="cell">905-555-8743</phone> 
  <phone type="cottage">613-555-3278</phone> 
</customerinfo>' PRESERVE WHITESPACE )
ACCORDING TO XMLSCHEMA ID posample.customer )); 

--------------------------------------------------------------------------------------------------

1 ligne insérée.

 

---

On peut aussi, toujours à l'aide d'un schéma, faire de la décomposition XML-> DB2

C'est à dire "parser" le XML afin de le faire correspondre ("mapper") aux colonnes d'une table

 

Les éléments du mappage dans le schéma :

• Ajouter l'espace de nommage db2-xdb

<xs:schema 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
    xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1" >

• Définir le schéma par défaut dans une annotation

   <xs:annotation>
      <xs:appinfo>
          <db2-xdb:defaultSQLSchema>POSAMPLE</db2-xdb:defaultSQLSchema>
      </xs:appinfo>
   </xs:annotation>

• Associer à un élément ou un attribut un nom de table et de zone

  
   <xs:element name="name" type="xs:string" minOccurs="1" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="NOM" />
ou
  <xs:attribute name="country" type="xs:string" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="PAYS" />
    

Remarque

tous les éléments n'ont pas à être associés à une colonne (on peut "perdre" des données)

la phase de transformation gère les éléments multiples, par exemple pour un client, un nom, une adresse et DES téléphones
 il y aura autant de lignes générées qu'il y a des N° de téléphone, nom et adresse étant répétés (un peu comme une jointure)

Ce schéma doit être enregistré, (nous devons toujours passer par une procédure, XSR_REGISTER attendant un paramètre de type BLOB)

CREATE PROCEDURE POSAMPLE.SAMPLE_DECOMP ( ) 
       LANGUAGE SQL 
       BEGIN 
       DECLARE CONTENT BLOB ( 1 M ) ; 
       VALUES BLOB('<?xml version="1.0"?>
<xs:schema targetNamespace="http://posample.org"
       xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
       xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1" >

 <xs:annotation>
   <xs:appinfo>
      <db2-xdb:defaultSQLSchema>POSAMPLE</db2-xdb:defaultSQLSchema>
   </xs:appinfo>
 </xs:annotation>
<xs:element name="customerinfo">
 <xs:complexType>
  <xs:sequence>
   <xs:element name="name" type="xs:string" minOccurs="1" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="NOM" />
   <xs:element name="addr" minOccurs="1" maxOccurs="unbounded">
   <xs:complexType>
    <xs:sequence>
     <xs:element name="street" type="xs:string" minOccurs="1" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="RUE" />
     <xs:element name="city" type="xs:string" minOccurs="1" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="VILLE" />
     <xs:element name="prov-state" type="xs:string" minOccurs="1" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="ETAT" />
     <xs:element name="pcode-zip" type="xs:string" minOccurs="1" />
    </xs:sequence>
    <xs:attribute name="country" type="xs:string" db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="PAYS" />
   </xs:complexType>
   </xs:element>
   <xs:element name="phone" nillable="true" minOccurs="0" maxOccurs="1" 
                                                            db2-xdb:rowSet="CUSTOMERD" db2-xdb:column="TEL">
    <xs:complexType>
      <xs:simpleContent>
       <xs:extension base="xs:string">
            <xs:attribute name="type" form="unqualified" type="xs:string" />
       </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
   </xs:element>
  </xs:sequence>
  <xs:attribute name="Cid" type="xs:integer" />
 </xs:complexType>
</xs:element>

</xs:schema>')
INTO CONTENT; 

 CALL SYSPROC . XSR_REGISTER ( 'POSAMPLE' , 'CUSTOMERD' , 'http://posample.org' , CONTENT , NULL ) ; 
END  ;

CALL POSAMPLE.SAMPLE_DECOMP;

CALL SYSPROC.XSR_COMPLETE('POSAMPLE', 'CUSTOMERD', null, 1);

le dernier paramètre sur XSR_COMPLETE indique si le schéma servira à une décomposition (1) ou non (0)

Vous pouvez voir aussi les schémas par la nouvelle option de System I Navigator (Décomposition représente ce dernier paramètre)

Puis la décomposition se fait à l'aide de la procédure XDBDECOMPXML qui attend un BLOB contenant un flux XML à décomposer.

  pour décomposer du XML stocké dans une table vous devez écrire une procédure qui balaye la table :

CREATE PROCEDURE POSAMPLE.DECOMP ( ) 
       LANGUAGE SQL 
       SET OPTION DBGVIEW = *SOURCE
BEGIN 
  DECLARE CONTENT BLOB ( 2G ) ; 
  DECLARE WEOF INTEGER DEFAULT 0;
 
  DECLARE not_found CONDITION FOR '02000';

  DECLARE c1 CURSOR FOR
         SELECT XMLSERIALIZE(info as BLOB(2G) ) FROM POSAMPLE.CUSTOMER;

  DECLARE CONTINUE HANDLER FOR not_found SET wEOF = 1;

  OPEN c1;
  FETCH c1 INTO content;
  WHILE wEOF = 0 DO

      CALL XDBDECOMPXML ('POSAMPLE' , 'CUSTOMERD' , content, NULL); 
      FETCH c1 INTO content;
  END WHILE;
  CLOSE c1;
END  ;

et voici le résultat

 

Les différentes annotations de l'espace de nommage db2-xdb :

• db2-xdb:defaultSQLSchema : bibliothèque par défaut
• db2-xdb:rowSet : nom de la table
• db2-xdb:column : nom de la zone
• db2-xdb:locationPath : chemin pour accéder à l'élément (s'il n'est pas racine)
• db2-xdb:expression : permet d'utiliser une expression SQL (pour CASTER, par ex) à la place de la valeur brute, à insérer
• db2-xdb:condition : permet d'utiliser une condition lors du mappage (pour choisir l'expression à utiliser, par exemple)
  
  voir les exemples, (nombreux, mais complexes) fournis avec la documentation officielle.

SF99701 level 4 apporte deux nouvelles annotations permettant de choisir l'ordre d'insertion dans les tables (importante pour l'intégrité référentielle)

• db2-xdb:rowSetOperationOrder : annotation parente pour un groupe db2-xdb:order
• db2-xdb:order : permet de spécifier l'ordre d'insertion dans une table

 

---

• la PTF SI46631 implémente une nouvelle fonction XMLTABLE produisant un contenu SQL à partir d'un flux XML

Exemple, Soit la table CUSTOMER

CREATE SCHEMA POSAMPLE; SET CURRENT SCHEMA POSAMPLE; CREATE TABLE CUSTOMER ( CID BIGINT NOT NULL PRIMARY KEY , INFO XML ) ;

Avec les données suivantes

INSERT INTO Customer (Cid, Info) VALUES (1000, '<customerinfo Cid="1000"> <name>Kathy Smith</name> <addr country="Canada"> <street>5 Rosewood</street> <city>Toronto</city> <prov-state>Ontario</prov-state> <pcode-zip>M6W 1E6</pcode-zip> </addr> <phone type="work">416-555-1358</phone> </customerinfo>') INSERT INTO Customer (Cid, Info) VALUES (1002, '<customerinfo Cid="1002"> <name>Jim Noodle</name> <addr country="Canada"> <street>25 EastCreek</street> <city>Markham</city> <prov-state>Ontario</prov-state> <pcode-zip>N9C 3T6</pcode-zip> </addr> <phone type="work">905-555-7258</phone> </customerinfo>') INSERT INTO Customer (Cid, Info) VALUES (1003, '<customerinfo Cid="1003"> <name>Robert Shoemaker</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Aurora</city> <prov-state>Ontario</prov-state> <pcode-zip>N8X 7F8</pcode-zip> </addr> <phone type="work">905-555-2937</phone> </customerinfo>')

SELECT X.NOM, X.RUE, X.VILLE  FROM Customer, 
 XMLTABLE ('$c/customerinfo' passing INFO as "c"
     COLUMNS
      NOM     	CHAR(30)    PATH 'name',
      RUE	VARCHAR(25) PATH 'addr/street',
      VILLE    VARCHAR(25)   PATH 'addr/city'
  ) AS X

Affiche

....+....1....+....2....+....3....+....4....+....5....+....6....+....7
NOM                             RUE                        VILLE      
Kathy Smith                     5 Rosewood                 Toronto    
Jim Noodle                      25 EastCreek		   Markham    
Robert Shoemaker                1596 Baseline              Aurora     
********  Fin de données  ********                                    

Si nous avions inséré les données de la manière suivante

INSERT INTO Customer (Cid, Info) VALUES (1000, '<customerinfo xmlns="http://posample.org" Cid="1000"> <name>Kathy Smith</name> <addr country="Canada"> <street>5 Rosewood</street> <city>Toronto</city> <prov-state>Ontario</prov-state> <pcode-zip>M6W 1E6</pcode-zip> </addr> <phone type="work">416-555-1358</phone> </customerinfo>') INSERT INTO Customer (Cid, Info) VALUES (1002, '<customerinfo xmlns="http://posample.org" Cid="1002"> <name>Jim Noodle</name> <addr country="Canada"> <street>25 EastCreek</street> <city>Markham</city> <prov-state>Ontario</prov-state> <pcode-zip>N9C 3T6</pcode-zip> </addr> <phone type="work">905-555-7258</phone> </customerinfo>') INSERT INTO Customer (Cid, Info) VALUES (1003, '<customerinfo xmlns="http://posample.org" Cid="1003"> <name>Robert Shoemaker</name> <addr country="Canada"> <street>1596 Baseline</street> <city>Aurora</city> <prov-state>Ontario</prov-state> <pcode-zip>N8X 7F8</pcode-zip> </addr> <phone type="work">905-555-2937</phone> </customerinfo>')

les éléments XML sont alors réputés comme appartenant à un espace de nommage (namespace)

le choix d'un élément se fait en indiquant l'espace de nommage ou * (tous) suivit de ":"

Il faut alors écrire :

 SELECT X.NOM, X.RUE, X.VILLE 
 FROM Customer,   
    XMLTABLE ('$c/*:customerinfo' passing INFO as "c"
      COLUMNS  
       NOM     CHAR(30)    PATH '*:name',
       RUE		VARCHAR(25) PATH '*:addr/*:street',
       VILLE   VARCHAR(25) PATH '*:addr/*:city' ) AS X 

Ou bien définir l'espace de nommage

 SELECT X.nom ,x.rue, x.ville, x.pays 
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES('http://posample.org' as "cli"),     
   '$c/cli:customerinfo' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'cli:name',             
      RUE    VARCHAR(25) PATH 'cli:addr/cli:street',          
      VILLE  VARCHAR(25) PATH 'cli:addr/cli:city',       
      PAYS   char(30)    PATH 'cli:addr/@country'          
  ) AS X        

Vous remarquerez le choix d'un attribut "@country"

Ou bien encore, définir un espace de nommage par défaut

 SELECT X.nom ,x.rue, x.ville, x.pays 
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'name',             
      RUE    VARCHAR(25) PATH 'addr/street',          
      VILLE  VARCHAR(25) PATH 'addr/city',       
      PAYS   char(30)    PATH 'addr/@country'          
  ) AS X                                            

S'il existe plusieurs numéros de téléphone ( par exemple insérons ceci)

INSERT INTO Customer (Cid, Info) VALUES (1000, '<customerinfo Cid="1005"> <name>Helen SUE</name> <addr country="US"><street>1596 Sunset BD</street> <city>L.A</city> <prov-state>California</prov-state> <pcode-zip>123456</pcode-zip> </addr> <phone type="work">999-555-1111</phone> <phone type="home">999-111-4444</phone> </customerinfo>

la fonction XMLTABLE ne peut pas mettre une série de valeur dans UNE colonne, vous recevez SQ16003

 SELECT X.nom ,x.rue, x.ville, x.TEL 
  FROM posample/Customer, 
 XMLTABLE ( 
  XMLNAMESPACES(DEFAULT 'http://posample.org'), 
   '$c/customerinfo' passing INFO as "c" 
  COLUMNS 
   NOM    CHAR(30)    PATH 'name', 
   RUE    VARCHAR(25) PATH 'addr/street', 
   VILLE  VARCHAR(25) PATH 'addr/city', 
   TEL    char(10)    PATH 'phone' 
 ) AS X 

An XPath expression has a type that is not valid for the context in which the expression occurs. Cause . . . . . : An Expression of data type ( item(), item()+ ) cannot be used when the data type item() is expected for the context. The following situations might cause this error: * For a constructor function of an XML Schema atomic type, atomization of its argument must not result in more than one atomic value. * The cast of a value of ( item(), item()+ ) to target item() must be a supported cast. * An XPath expression was used in an output column of the XMLTABLE built in table function that could not be casted to the expected type. This typically happens when the expression result contains more than one atomic value and a singleton is expected; or a cast from the result type to the expected data type is not supported. The XPath expression cannot be processed. Recovery . . . : Specify a value of the correct type. Technical description . . . . . . . . : Error QName=err:XPTY0004.

vous pouvez alors :

->ne recevoir que la(les) première(s) valeur(s)

    Ecrivez

 SELECT X.nom ,x.rue, x.ville, x.TEL                        
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'name',             
      RUE    VARCHAR(25) PATH 'addr/street',          
      VILLE  VARCHAR(25) PATH 'addr/city',       
      TEL    char(10)    PATH 'phone[1]'          
  ) AS X                                            

->retourner la totalité des téléphones sous forme XML

 SELECT X.nom ,x.rue, x.ville, x.TEL                        
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'name',             
      RUE    VARCHAR(25) PATH 'addr/street',          
      VILLE  VARCHAR(25) PATH 'addr/city',       
      TEL    XML		  PATH 'phone'          
  ) AS X                    

-> retourner chaque téléphone avec la syntaxe suivante

SELECT X.nom ,x.rue, x.ville, x.TEL                      
  FROM posample.Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo/phone' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH '../name',             
      RUE    VARCHAR(25) PATH '../addr/street',          
      VILLE  VARCHAR(25) PATH '../addr/city',       
      TEL     char(20)   PATH '.'          
  ) AS X       

ici, nous considérons customer/phone comme l'élément "racine" (d'où le PATH '.' ) , pour les autres éléments nous remontons d'un cran par ".." .

 

La clause FOR ORDINALITY permet d'ajouter un compteur indiquant l'ordre d'apparition de l'élement dans le flux XML.

SELECT X.NOM, X.RUE, X.VILLE, X.NUMERO  FROM Customer,
   XMLTABLE ('$c/customerinfo' passing INFO as "c"
      COLUMNS   NOM     	CHAR(30)    PATH 'name',
      RUE		VARCHAR(25) PATH 'addr/street',
      VILLE     VARCHAR(25)   PATH 'addr/city'
	     NUMERO    FOR ORDINALITY
) AS X

Affiche
 ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....
 NOM                             RUE                        VILLE       NUMERO
 Kathy Smith                     5 Rosewood                 Toronto        1
 Jim Noodle                      25 EastCreek		    Markham        1
 Robert Shoemaker                1596 Baseline              Aurora         1
   ********  Fin de données  ******** 

Notez que le compteur est propre à CHAQUE document XML, soit ici à Chaque /customerinfo



Ci dessous numérotation de chaque "phone" dans customerinfo:

 SELECT X.nom ,x.rue, x.ville, x.TEL                      
  FROM posample.Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo/phone' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH '../name',             
      RUE    VARCHAR(25) PATH '../addr/street',          
      VILLE  VARCHAR(25) PATH '../addr/city',       
      TEL     char(20)   PATH '.'   
      NUMERO    FOR ORDINALITY 
  ) AS X                         


Affiche
 ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....
 NOM                    RUE                  VILLE        TEL         NUMERO
Kathy Smith           5 Rosewood            Toronto   416-555-1358       1
Jim Noodle            1150 Maple Drive      Newtown   905-555-7258       1
Robert Shoemaker      25 EastCreek          Markham   905-555-2937       1
Helen SUE             1596 Sunset BD        L.A       999-555-1111       1
Helen SUE             1596 Sunset BD        L.A       999-111-4444       2
   ********  Fin de données  ******** 

Il est aussi possible de faire des sélections sur les éléments XML

 SELECT X.nom ,x.rue, x.ville                        
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo[addr/@country = "Canada"]' passing INFO as "c"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'name',             
      RUE    VARCHAR(25) PATH 'addr/street',          
      VILLE  VARCHAR(25) PATH 'addr/city'      
  ) AS X                    

ou bien

SELECT X.nom ,x.rue, x.ville                        
  FROM posample/Customer,                           
 XMLTABLE (                                         
  XMLNAMESPACES(DEFAULT 'http://posample.org'),     
   '$c/customerinfo[addr/@country = $pays]' passing INFO as "c", 'canada' as "pays"               
     COLUMNS                                        
      NOM    CHAR(30)    PATH 'name',             
      RUE    VARCHAR(25) PATH 'addr/street',          
      VILLE  VARCHAR(25) PATH 'addr/city'      
  ) AS X                    

Vous remarquerez la syntaxe XPATH déjà utilisée avec OmniFind et maintenant documentée sur Information Center

 

Si la sélection XPATH ne retourne pas de données, les colonnes base de données de cette ligne ne sont pas affichées

SAUF à utiliser LEFT JOIN

Après avoir ajouté un client (1004) Français

SELECT CID, X.nom ,x.rue, x.ville 
 FROM posample/Customer LEFT JOIN 
 XMLTABLE ( 
  XMLNAMESPACES(DEFAULT 'http://posample.org'), 
     '$c/customerinfo[addr/@country = $pays]' passing INFO as "c", 'canada' as "pays"  
  COLUMNS 
    NOM    CHAR(30)    PATH 'name', 
    RUE    VARCHAR(25) PATH 'addr/street', 
    VILLE  VARCHAR(25) PATH 'addr/city' 
 ) AS X on 1=1

 ....+....1....+....2....+....3....+....4....+....5....+....6....+....7.... 
 1.000   Kathy Smith                     5 Rosewood                 Toronto 
 1.002   Jim Noodle                      25 EastCreek		         Markham 
 1.003   Robert Shoemaker                1596 Baseline              Aurora 
 1.004   -                               -                          - 

Remarquez la dernière ligne ou les colonnes venant de XMLTABLE sont à valeur nulle

Cette syntaxe XPATH peut être utilise avec les dates, en effet les dates ont une notion de fuseau horaire en XML et sont censées être UTC (soit GMT) en SQL.
or en France nous sommes à +1(l'hiver) ou +2(l'été).

Exemples de dates valides en XML :

--------------------------------------------
2017-10-19
2017-10-19Z
2017-10-19+02:00
17:20:30
17:20:30+02:00
2017-10-19T17:20:30
2017-10-19T17:20:30+02:00

Si une information avec fuseau Horaire est stockée en XML , SQL la transforme en UTC

Exemples de transformation XML->SQL :
--------------------------------------------------
2017-10-19->2017-10-19
2017-10-19Z->2017-10-19
2017-10-19+02:00->2017-10-18 (et oui en GMT 2017-10-18 à 22h00)
17:20:30->17.20.30
17:20:30+02:00->15.20.30
2017-10-19T17:20:30->2017-10-19-17.20.30
2017-10-19T17:20:30+02:00->2017-10-19-15.20.30



la documentation précise :

if needed use   fn:adjust-date-to-timezone() function to explicitly set the time zone

 

Enfin, nous pouvons utiliser XMLTABLE directement à partir d'un fichier de l'IFS

 

SELECT XML_IFS.*  FROM 
(VALUES(  XMLPARSE(DOCUMENT GET_XML_FILE('/xml/vins.xml'))
  )) AS XMLFILE(VIN),
    XMLTABLE(
   '$doc/vins/vin' PASSING XMLFILE.VIN AS "doc"
 COLUMNS
  nom    VARCHAR(32) PATH 'nom', 
  cepage VARCHAR(64) PATH 'cepage1')
 AS XML_IFS

OU

SELECT * FROM 
    XMLTABLE(
      '$doc/vins/vin' PASSING XMLPARSE(DOCUMENT GET_XML_FILE('/xml/vins.xml')) AS "doc"
 COLUMNS
  nom    VARCHAR(32) PATH 'nom', 
  cepage VARCHAR(64) PATH 'cepage1')
 AS XML_IFS

 

Exemple à partir de ce fichier : cours.xml

SELECT cours, texte, motcle1,  monthname(modif) concat '-' concat year(modif)
 FROM XMLTABLE('$result/AF400/COURS' 
  PASSING XMLPARSE(
        DOCUMENT 
        GET_XML_FILE('/formation/cours.xml')
  ) as "result"
 COLUMNS 
   cours CHAR(10) PATH 'AF4MBR',
   texte CHAR(50) PATH 'AF4TXT',
   motcle1 VARCHAR(20) PATH 'MOTCL1',
   MODIF DATE PATH 'DATOUT'
 ) AS TABLEXML;

---

Enfin la PTF SF99701, level 23 apporte de nouvelles fonctions orientées web services

(Attention la PTF SF99701 level 22 est déclarée en erreur, ne l'installez pas, installez le level 23 à la place ou la SI49730)

• httpGetBlob
  httpGetClob
  httpPutBlob
  httpPutClob
  httpPostBlob
  httpPostClob
  httpDeleteBlob
  httpDeleteClob
  httpBlob
  httpClob
  httpHead
   
• UrlEncode
  UrlDecode
  base64Encode
  base64Decode
  

le but de ces fonctions est de consommer des services web plutôt orientés RESTful


Regardons à travers des exemples

Values SYSTOOLS.HTTPGETCLOB('http://www.volubis.fr' ,'') ;

Récupère dans une variable le contenu de notre page d'accueil

Le premier paramètre est de type VARCHAR(2048) et représente une URL

Le deuxième paramètre est de type CLOB(10K) et représente l'entête http

• vide ( '' ) , aucune entête n'est transmise
• une chaine représentant l'entête HTTP, sous la forme
  <httpHeader [options]>
     <header name="entete" value="valeur" />
  </httpHeader>
  
  options possibles
  • ConnectionTimeout = millisecondes
  • readTimeout = millisecondes
  • followRedirects = true|false (suivre les redirections en cas de status 3xx)
  • useCaches = true|false (utilise rle cache, voir paramètres de la JVM)
    
    
• Voir les entêtes possible http://en.wikipedia.org/wiki/List_of_HTTP_header_fields
  

La valeur retour est la réponse fournie par le serveur (ce que vous aurait affiché votre navigateur)

Pour les fonctions HTTPPOSTBLOB|CLOB et HTTPPUTBLOB|CLOB, il y un troisème paramètre: les données à transmettre
(avec GET les paramètres sont dans l'URL)

     
     Ce code permet d'invoquer via HTTP, une page PHP en utilisant la méthode POST.
     (utilisation du deuxième paramètre permettant de fournir les entêtes http)
     
     -- LECTURE page WEB via POST
     VALUES  SYSTOOLS.HTTPPOSTCLOB('http://serveur/php/exemples/tp.php',
 	                    CAST('<httpHeader>
                               <header name="Content-Type values="application/x-www-form-urlencoded />'
                                  </httpHeader>') as CLOB(5k) ,
                             CAST('variable1=valeur1&variable2=valeur2' AS CLOB(5k) ) );
     

  

Si le site retourne du XML, l'utilisation de la fonction XMLTABLE permettera de ne recevoir que les données utiles

Ici, le site www.redbooks.ibm.com propose des flux RSS donnant les publications récentes

   le code

SELECT *
 FROM XMLTABLE('$result/rss/channel/item' 
 PASSING XMLPARSE(
  DOCUMENT 
     SYSTOOLS.HTTPGETBLOB('http://www.redbooks.ibm.com/rss/iseries.xml','')
  ) as "result"
 COLUMNS 
  title VARCHAR(128) PATH 'title',
  description VARCHAR(1024) PATH 'description',
  link VARCHAR(255) PATH 'link',
  pubDate VARCHAR(20) PATH 'substring(pubDate, 1, 16)'
 ) AS RESULT; 

permet de lire les données de manière structurée :

Ce code permet de lire le fichier cours.xml , s'il est disponible via HTTP

SELECT cours, texte, motcle1,  monthname(modif) concat '-' concat year(modif)
 FROM XMLTABLE('$result/AF400/COURS' 
  PASSING XMLPARSE( 
  DOCUMENT 
   SYSTOOLS.HTTPGETBLOB('http://as400.volubis.intra/af4dir/courshtm/XML/cours.xml','')
 ) as "result"
 COLUMNS 
  cours CHAR(10) PATH 'AF4MBR',
  texte CHAR(50) PATH 'AF4TXT',
  motcle1 VARCHAR(20) PATH 'MOTCL1',
  MODIF DATE PATH 'DATOUT'
 ) AS TABLEXML;  


Ce code permet de le lire via HTTP, la page étant protégée par un mot de passe

-- LECTURE fichier XML via INTERNET avec authentification
SELECT cours, texte, motcle1,  monthname(modif) concat '-' concat year(modif)
   FROM XMLTABLE('$result/AF400/COURS' 
      PASSING XMLPARSE(
         DOCUMENT 
            SYSTOOLS.HTTPGETBLOB('http://af400:motdepasse@as400.volubis.fr/af4dir/courshtm/XML/cours.xml','')
      ) as "result"
         COLUMNS 
            cours CHAR(10) PATH 'AF4MBR',
            texte CHAR(50) PATH 'AF4TXT',
            motcle1 VARCHAR(20) PATH 'MOTCL1',
            MODIF DATE PATH 'DATOUT'
   ) AS TABLEXML;

Vous pouvez aussi passer l'authentification dans les entêtes : Profil et mot de passe séparés par ":" (comme dans notre exemple) en UTF-8 puis encodé en Base64 (voir la fonction base64ENCODE) enfin précédé du mot "basic" <header name="Authorization" value="Basic bmljazpwYXNzdzByZA=="/>

Appel d'un Web services, l'enveloppe SOAP est fournie dans le 3eme paramètre
(ici, nous demandons la valeur de l'action IBM à aujourd'hui dans une enveloppe SOAP)

 
-- Appel d'un web service , récupération de l'enveloppe SOAP réponse dans une variable
VALUES SYSTOOLS.HTTPPOSTCLOB('http://www.webservicex.net//stockquote.asmx',
	CAST ('<httpHeader>
       	<header name="Content-Type" value="text/xml;charset=utf-8"/>
       	<header name="SOAPAction" value="&quot;http://www.webserviceX.NET/GetQuote&quot;"/>
       	</httpHeader>' AS CLOB(1K)),
 	CAST('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
          	<soap:Body>
          	   <GetQuote xmlns="http://www.webserviceX.NET/">
          	      <symbol>IBM</symbol>
          	   </GetQuote>
          	</soap:Body>
      	 </soap:Envelope>' AS CLOB(10K))  )  ;

  

-- Appel d'un web service , récupération de l'enveloppe SOAP, "parsée"
-- il y a des espaces de nommage (xmlns) SOAP et d'autres propres au web service, d'où -> *:
-- les données extraites sont elles mêmes au format XML (on voit que l'action est à 210 $ 55 )
     SELECT*
     FROM XMLTABLE('$result/*:Envelope/*:Body/*:GetQuoteResponse'
     PASSING XMLPARSE(
     DOCUMENT 
     SYSTOOLS.HTTPPOSTCLOB('http://www.webservicex.net//stockquote.asmx',
     CAST ('<httpHeader>
             <header name="Content-Type" value="text/xml;charset=utf-8"/>
             <header name="SOAPAction" value="&quot;http://www.webserviceX.NET/GetQuote&quot;"/>
            </httpHeader>' AS CLOB(1K)),
     CAST('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            <soap:Body>
             <GetQuote xmlns="http://www.webserviceX.NET/">
               <symbol>IBM</symbol>
             </GetQuote>
            </soap:Body>
           </soap:Envelope>' AS CLOB(10K))  ) 
     ) as "result"
     COLUMNS 
     resultat  VARCHAR(2000) PATH '*:GetQuoteResult'
     ) AS TABLEXML;
   

-- Appel d'un web service , récupération de l'enveloppe SOAP, "parsée" pour lire le montant de l'action
-- puis découpage du XML extrait à nouveau par la fonction XMLTABLE
WITH temp as (SELECT resultat
     FROM XMLTABLE('$result/*:Envelope/*:Body/*:GetQuoteResponse'
     PASSING XMLPARSE(
     DOCUMENT 
     SYSTOOLS.HTTPPOSTCLOB('http://www.webservicex.net//stockquote.asmx',
     CAST ('<httpHeader>
           <header name="Content-Type" value="text/xml;charset=utf-8"/>
           <header name="SOAPAction" value="&quot;http://www.webserviceX.NET/GetQuote&quot;"/>
           </httpHeader>' AS CLOB(1K)),
     CAST('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            <soap:Body>
              <GetQuote xmlns="http://www.webserviceX.NET/">
               <symbol>IBM</symbol> 
              </GetQuote>
            </soap:Body>
           </soap:Envelope>' AS CLOB(10K))  ) 
     ) as "result"
     COLUMNS 
      resultat  VARCHAR(2000) PATH '*:GetQuoteResult'
     ) AS TABLEXML
   )

select * from temp, 
     XMLTABLE ('$c/StockQuotes/Stock' passing XMLPARSE(DOCUMENT RESULTAT) as "c"
     COLUMNS 
     symbol     CHAR(30)    PATH 'Symbol',
     prix 	dec(11, 2 )    PATH 'Last' 
) AS X ;

Les mails GMAIL sont accessibles depuis un navigateur (c'est même un peu le principe de base)


Mais aussi en web service REST :
  

-- lecture de mes mails chez google, penser à changer le mot de passe -;)

SELECT *
     FROM XMLTABLE('$result/*[local-name()=''feed'']/*[local-name()=''entry'']' 
             PASSING 
             XMLPARSE(DOCUMENT 
                 systools.HTTPGETBLOB('https://af400Volubis:motdepasse@gmail.google.com/mail/feed/atom/','')) 
              AS "result"
          COLUMNS 
              title VARCHAR(128) PATH '*[local-name()=''title'']',
              summary VARCHAR(1024) PATH '*[local-name()=''summary'']',
              author_name VARCHAR(255) PATH '*[local-name()=''author'']/*[local-name()=''name'']',
              author_email VARCHAR(255) PATH '*[local-name()=''author'']/*[local-name()=''email'']'
     ) AS RESULT;

-> Remarquez la fonction XPATH [local-name()= ] permettant de recherche un élément quelque soit sa localisation dans l'arborescence

 

• URLENCODE permet de coder une chaîne au format compatble URL
  (le deuxième caractère, optionnel est un code page)
  
• URLDECODE fait le contraire
  
  
• base64ENCODE encode une donnée en base 64
  
• bien sûr base64DECODE fait le contraire

• Les fonction HTTP ont leur équivalent en mode VERBOSE, ce sont alors des fonctions table, retournant :
  1. le message (comme la fonction simple)
  2. l'entête
     

httpGetBlobVerbose

httpGetClobVerbose

httpPutBlobVerbose

httpPutClobVerbose

httpPostBlobVerbose

httpPostClobVerbose

httpDeleteBlobVerbose

httpDeleteClobVerbose

httpBlobVerbose

httpClobVerbose

httpHeadVerbose

qui s'utilisent comme ça

with temp as (
       select responsemsg as msg , responsehttpheader as header 
from table( systools.httpgetclobverbose('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml' ,'')) as E)

select code, monnaie, taux from temp CROSS JOIN 

xmltable('$r/httpHeader' passing xmlparse(DOCUMENT header) as "r"
       COLUMNS
       code char(25) PATH '@responseCode',
       message char(25) PATH 'responseMessage'
       ) as x
  CROSS JOIN
xmltable('$m/*:Envelope/*:Cube/*:Cube/*:Cube' passing xmlparse(DOCUMENT msg) as "m"
       COLUMNS
       monnaie char(3) PATH '@currency',
       taux  dec(11, 7)  PATH '@rate'
       ) as y

---

Vous pouvez mettre des options à java, en créant un fichier d'options

-Xmx	Mémoire pour java (par exemple -Xmx2g -> 2 Go)
http.proxyHost	Coordonnées du Proxy
http.proxyPort	port du proxy (80 par défaut)
http.proxyUser	Utilisateur pour proxy
http.proxyPassword	Mot de passe pour proxy
http.nonProxyHosts	Liste des serveurs pour lesquels ne pas utiliser le proxy
javax.net.ssl.trustStore	emplacement du fichier contenant la liste des certificats des sites de confiance
javax.net.ssl.trustStorePassword	mot de passe pour ouvrir le fichier trustStore
javax.net.ssl.keyStore	emplacement du fichier contenant la liste des certificats et des clés privées (keystore)
javax.net.ssl.keyStorePassword	mot de passe pour ouvrir le fichier keyStore

pour ces dernières options, concernant l'accès HTTPS, voyez notre cours SSL

Le fichier de propriétés java peut être indiqué comme suit

1. en créant une variable d'environnement indiquant sa localisation
   

   ADDENVVAR ENVVAR(QIBM_JAVA_PROPERTIES_FILE)
            VALUE(/QIBM/userdata/java400/mySystem.properties)     

2. en créant un fichier SystemDefault.properties dans la home directory de l'utilisateur
3. en créant un fichier SystemDefault.properties dans /QIBM/userdata/java400/

Attention Toutes ces fonctions sont livrées dans SYSTOOLS, donc "as is' et ne peuvent faire l'objet d'une demande de support