DBeaver Oracle : Extraire la valeur d'une balise d'un xml

Version imprimable

1 pièce(s) jointe(s)

:coucou: Forum,
J'espère que tout le monde se porte bien.

Dans ma table INBOX_MESSAGES, j'ai un colonne CONTENT qui contient un xml.
Quand je veux extraire une valeur correspondant à une balise, j'utilise ce code :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT :mybalise AS Balise ,SUBSTR(im.CONTENT,INSTR(im.CONTENT, :mybalise) + LENGTH(:mybalise) + 1, ((INSTR(im.CONTENT, '/' || '' || :mybalise)-2) - (INSTR(im.CONTENT, :mybalise) + LENGTH(:mybalise) + 1)+1)) AS BALISE_VALUE FROM INBOX_MESSAGES im INNER JOIN PERSONS p2 ON p2.PERSON_ID =im.PERSON_ID WHERE im.TYPE = 'D054' AND im.OVERALL_START_DATE >= '01/07/2020' AND im.OVERALL_START_DATE <= '30/09/2020' ;
Pièce jointe 582969

Oui, je sais, cela ressemble furieusement à de l'excel transformé pour la cause. C'est vrai mais cela fonctionne.
Mais, je suis certain qu'il existe un moyen plus simple de trouver la balise et sa valeur. J'ai essayé avec XMLTABLE, XMLQUERY... rien n'y fait.
Si vous avez une très bonne piste, je suis preneur.

04/11/2020, 14h37
vanagreg

OMG, ce n'est pas du tout comme cela qu'il faut faire. Il faut en effet utiliser Xquery. Ici tu peux faire un XMLTABLE en précisant les namespaces de ton fichier xml. Ensuite tu précises le chemin où se trouve l'info.
04/11/2020, 14h51
QuestVba

Bonjour vanagreg,
Je sais que tu as hésité :zekill: :scarymov: :massacre: :rouleau: :mur:

Effectivement je ne m'en sors pas avec XMLTABLE.

Ma façon de faire est tout ce qu'il ne faut pas faire mais elle est simple car j'encode uniquement le nom de la balise et il me retourne la valeur. Pas besoin d'inclure un chemin, surtout qu'il pourrait être différent ; ben oui, selon le flux, le xml contient certaines parties et pas d'autres.

Mais si tu as un lien vers une explication facile pour être dans le sql pur, merci de me le fournir. Car avec ce que j'ai trouvé, je n'y arrive pas. Merci, merci...
04/11/2020, 15h06
vanagreg

J'avoue que ce n'est pas forcément évident à comprendre au début, et même mois je me considère comme débutant :roll:
J'ai bien galéré au début quand il faut gérer des namespaces, et d'ailleurs j'ai toujours besoin de retrouver mes exemples passés pour me débrouiller.
Je ne sais pas s'il existe des ressources qui expliquent comment utiliser XMLTABLE sur ce site, je suppose que oui, mais je ne sais pas où, d'autres seront mieux te renseigner.

Le problème avec l'utilisation combinée de substr et instr, c'est que ça va vite ressembler à une usine à gaz si l'info se répète à plusieurs endroits, et que tu dois extraire toutes les valeurs. Sans parler de performances si ton document XML fait plusieurs centaines de Mo par exemple.
06/11/2020, 11h08
QuestVba

Bon, j'ai encore cherché mais rien n'y fait. Je n'arrive pas vraiment à trouver ce que je veux.
C'est assez incroyable de ne pas arriver à lire la valeur d'un balise.
06/11/2020, 11h48
vanagreg

Bonjour,

Montre ce que tu as fait pour voir. Tu peux remplacer les valeurs sensibles par des "xxxxxx" si besoin.

Bonjour QuestVBA,

Voici un exemple.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
 WITH x AS  (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<HF_DOCUMENT>
	<INVENTAIRE>
		<societe>1</societe>
		<magasin>2</magasin>
		<article>75987</article>
		<ZONE>3</ZONE>
		<emplacement>J0100</emplacement>
		<date_inventaire>20201008</date_inventaire>
		<qte_pal>1</qte_pal>
		<qte>5</qte>
		<COMMENTAIRE null="yes"></COMMENTAIRE>
	</INVENTAIRE>
	<INVENTAIRE>
		<societe>1</societe>
		<magasin>2</magasin>
		<article>75986</article>
		<ZONE>3</ZONE>
		<emplacement>J0200</emplacement>
		<date_inventaire>20201008</date_inventaire>
		<qte_pal>1</qte_pal>
		<qte null="yes"></qte>
		<COMMENTAIRE>Tout vide</COMMENTAIRE>
	</INVENTAIRE>
</HF_DOCUMENT>') xml_data
FROM dual)
SELECT xt.*
FROM x, XMLTABLE('/HF_DOCUMENT/INVENTAIRE'
         PASSING x.xml_data
         COLUMNS 
                societe     			NUMBER  PATH 'societe',
                magasin     	NUMBER  PATH 'magasin',
                article     	NUMBER PATH 'article',
                ZONE     			NUMBER PATH 'ZONE',
                emplacement   VARCHAR2(10)  PATH 'emplacement',
                date_inventaire     NUMBER PATH 'date_inventaire',
                qte_pal  			NUMBER PATH 'qte_pal',
                qte  					NUMBER PATH 'qte',
                commentaire   VARCHAR2(2000)  PATH 'COMMENTAIRE'
            ) xt

Retour :

Code:

1
2
3
SOCIETE	MAGASIN	ARTICLE	ZONE	EMPLACEMENT	DATE_INVENTAIRE	QTE_PAL	QTE	COMMENTAIRE
1	2	75987	3	J0100	20201008	1	5	
1	2	75986	3	J0200	20201008	1		Tout vide

Vous pouvez aussi utiliser XMLSequence si vous ciblez simplement quelques morceaux, en reprenant l'exemple de McM :
Code:

1 2 3 4 5 6 7 8 select extractvalue(d.column_value, 'INVENTAIRE/article') as Article FROM x , table(XMLSequence(extract(x.xml_data, '/HF_DOCUMENT/INVENTAIRE'))) d where existsNode(d.column_value, '/INVENTAIRE[emplacement="J0200"]') = 1; ARTICLE ------- 75986

07/11/2020, 10h29
vanagreg

Les fonctions EXTRACTVALUE et EXISTSNODE sont dépréciées depuis la 11g.

Bonjour à tous,

Voici le xml qui se trouve dans la DB.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--Build-Version: 1.522-REV3ecdeb1e8b-20201007T0802--><tns:Certificate xmlns:tns="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054" xmlns:xsi="http://www.Blabla.org/2001/XMLSchema-instance" xsi:schemaLocation="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054 D054_v3.xsd">
  <Header>
    <MessageId>D054</MessageId>
    <SocialFund>599</SocialFund>
    <Office>87</Office>
    <FileNumber>9653236</FileNumber>
    <Role>106</Role>
    <IntegrationPeriod>
      <StartDate>2019-10-01</StartDate>
    </IntegrationPeriod>
    <ProcessDate>2020-10-16T20:47:00</ProcessDate>
    <Reference NRK="376babae0a6ea06e546982eb75d0c85a" Requestor="PN3DS1E95986I72"/>
    <Person>
      <PersonINSS>99121232165</PersonINSS>
      <PersonName>BOUCHE</PersonName>
      <PersonFirstName>MARTIN</PersonFirstName>
      <PersonDateOfBirth>
        <Day>---12</Day>
        <Month>--12</Month>
        <Year>1999</Year>
      </PersonDateOfBirth>
    </Person>
    <KSZMailboxNbr>02031200287520</KSZMailboxNbr>
  </Header>
  <Body>
    <DMFA>
      <CertificateIdentification>6445280141</CertificateIdentification>
      <CertificateSituationNbr>6985580141</CertificateSituationNbr>
      <CertificateStatus>0</CertificateStatus>
      <CertificateCreationDate>2020-10-16</CertificateCreationDate>
      <PLAIndicator>1</PLAIndicator>
      <EmployerDeclaration>
        <Quarter>
          <QuarterNumber>3</QuarterNumber>
          <Year>2020</Year>
        </Quarter>
        <NOSSRegistrationNbr>114126412</NOSSRegistrationNbr>
        <Trusteeship>0</Trusteeship>
        <CompanyID>446445265</CompanyID>
        <Person>
          <PersonINSS>99121232165</PersonINSS>
          <OriolusValidationCode>0</OriolusValidationCode>
          <WorkerRecord>
            <EmployerClass>68</EmployerClass>
            <WorkerCode>13</WorkerCode>
            <NOSSQuarterStartingDate>2020-07-01</NOSSQuarterStartingDate>
            <NOSSQuarterEndingDate>2020-09-30</NOSSQuarterEndingDate>
            <Border>0</Border>
            <Occupation>
              <OccupationSequenceNbr>1</OccupationSequenceNbr>
              <LocalUnitID>2146752614</LocalUnitID>
              <OccupationStartingDate>2018-10-01</OccupationStartingDate>
              <JointCommissionNbr>140.02</JointCommissionNbr>
              <ContractType>0</ContractType>
              <RefMeanWorkingHours>3800</RefMeanWorkingHours>
              <MeanWorkingHours>3800</MeanWorkingHours>
              <RegionalizationCodeDeduction>100</RegionalizationCodeDeduction>
              <LocalUnitCityNISCode>21014</LocalUnitCityNISCode>
              <Service>
                <ServiceSequenceNbr>1</ServiceSequenceNbr>
                <ServiceCode>1</ServiceCode>
                <ServiceNbrDays>300</ServiceNbrDays>
              </Service>
              <Service>
                <ServiceSequenceNbr>2</ServiceSequenceNbr>
                <ServiceCode>77</ServiceCode>
                <ServiceNbrDays>6300</ServiceNbrDays>
              </Service>
            </Occupation>
          </WorkerRecord>
        </Person>
      </EmployerDeclaration>
    </DMFA>
  </Body>
</tns:Certificate>

:coucou: McM
Quand j'adapte ton code comme ceci, tout fonctionne parfaitement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 WITH x AS  (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="no"?>
 
<Header>
	<INVENTAIRE>
		<societe>7</societe>
		<CertificateIdentification>6429580141</CertificateIdentification>
	</INVENTAIRE>
	<INVENTAIRE>
		<societe>5</societe>
		<CertificateIdentification>6429580143</CertificateIdentification>
	</INVENTAIRE>
</Header>') xml_data
FROM dual)
SELECT xt.*
FROM x, XMLTABLE('/Header/INVENTAIRE'
         PASSING x.xml_data
         COLUMNS 
                societe     			NUMBER  PATH 'societe',
        		Certfi					NUMBER PATH 'CertificateIdentification'
            ) xt
;

Mais quand j'inclus tout le début du xml, cela plante.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 WITH x AS  (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--Build-Version: 1.522-REV3ecdeb1e8b-20201007T0802--><tns:Certificate xmlns:tns="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054" xmlns:xsi="http://www.Blabla.org/2001/XMLSchema-instance" xsi:schemaLocation="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054 D054_v3.xsd">
 
<Header>
	<INVENTAIRE>
		<societe>7</societe>
		<CertificateIdentification>6429580141</CertificateIdentification>
	</INVENTAIRE>
	<INVENTAIRE>
		<societe>5</societe>
		<CertificateIdentification>6429580143</CertificateIdentification>
	</INVENTAIRE>
</Header>') xml_data
FROM dual)
SELECT xt.*
FROM x, XMLTABLE('/Header/INVENTAIRE'
         PASSING x.xml_data
         COLUMNS 
                societe     			NUMBER  PATH 'societe',
        		Certfi					NUMBER PATH 'CertificateIdentification'
            ) xt
;

J'ai cette erreur :

Code:

1
2
3
4
5
SQL Error [31011] [99999]: ORA-31011: Echec d'analyse XML
ORA-19202: Une erreur s'est produite lors du traitement la fonction XML (
LPX-00007: unexpected end-of-file encountered)
ORA-06512: à "SYS.XMLTYPE", ligne 310
ORA-06512: à ligne 1

J'ai essayé de modfier le sql mais rien n'y fait. Aurais-tu une piste ?

Il te manque la dernière ligne pour que ton XML soit valide (tu peux vérifier sous Chrome)

Code:

1
2
3
<tns:Certificate xmlns:tns="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054" xmlns:xsi="http://www.Blabla.org/2001/XMLSchema-instance" xsi:schemaLocation="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054 D054_v3.xsd">
 ...
</tns:Certificate>

Par contre je ne sais pas trop comment rajouter ce :Certificate au code (/tns:Certificate/Header/INVENTAIRE' plante)

Par contre j'ai vu sur https://www.viralpatel.net/oracle-xmltable-tutorial/ : // permet de prendre tous les éléments où qu'ils soient, donc tu pourrais récupérer les INVENTAIRES comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH x AS  (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--Build-Version: 1.522-REV3ecdeb1e8b-20201007T0802-->
<tns:Certificate xmlns:tns="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054" xmlns:xsi="http://www.Blabla.org/2001/XMLSchema-instance" xsi:schemaLocation="https://appprod.blabla.be/Blabla/documents/Docs/XSDs/v3/D054 D054_v3.xsd">
 <Header>
	<INVENTAIRE>
		<societe>7</societe>
		<CertificateIdentification>6429580141</CertificateIdentification>
	</INVENTAIRE>
	<INVENTAIRE>
		<societe>5</societe>
		<CertificateIdentification>6429580143</CertificateIdentification>
	</INVENTAIRE>
</Header>
</tns:Certificate>') xml_data
FROM dual)
SELECT xt.*
FROM x, XMLTABLE('//INVENTAIRE'
         PASSING x.xml_data
         COLUMNS 
                societe     			NUMBER  PATH 'societe',
        		Certfi					NUMBER PATH 'CertificateIdentification'
            ) xt

Merci, merci... Voilà ce qu'il me fallait. Un grand merci McM.
En fait, je me suis aussi rendu compte que mon xml était dans un CLOB.

Voici le résultat final :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 WITH x AS ( SELECT xmltype.createxml(im.CONTENT) xml_data ,im.FILE_NUMBER AS FileN ,p2.INSS AS niss FROM inbox_messages im INNER JOIN PERSONS p2 ON p2.PERSON_ID =im.PERSON_ID WHERE im.TYPE = 'D054' AND im.OVERALL_START_DATE >= '01/07/2020' AND im.OVERALL_START_DATE <= '30/09/2020' ) SELECT xt.* , x.niss , x.FileN FROM x, XMLTABLE('//DMFA' PASSING x.xml_data COLUMNS Certif_Date DATE PATH 'CertificateCreationDate', Certif_ID NUMBER PATH 'CertificateIdentification' ) xt ;
Le TOP du TOP serait de pouvoir se passer du chemin et de lui dire de trouver lui-même - comme un grand - la donnée là où elle se trouve. Une sorte de super //

Voilà au final sans le node dans le Xpath :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    xt.*
    ,im.file_number AS FileN
    ,per.inss AS niss
FROM
    inbox_messages im
    INNER JOIN persons per ON per.person_id = im.person_id 
    ,XMLTABLE
        ('*'
         PASSING XMLTYPE.CREATEXML(im.CONTENT)
         COLUMNS 
            Certif_Date DATE    PATH '//CertificateCreationDate',
            Certif_ID   NUMBER  PATH '//CertificateIdentification'
        ) AS xt
WHERE im.TYPE = 'D054'
    AND im.overall_start_date BETWEEN '01/07/2020' AND '30/09/2020'
;

12/11/2020, 15h15
McM

En effet, mais ça ne fonctionne que si les éléments ne sont présents qu'une seule fois, sinon erreur [Error] Execution (1: 1): ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
13/11/2020, 06h27
QuestVba

Effectivement, McM.
C'est un bémol.