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

**QuestVba** · 04/11/2020, 14h21

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 :

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

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

Nom : Image 9.png
Affichages : 506
Taille : 487,3 Ko

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.

**vanagreg** · 04/11/2020, 14h37

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.

**QuestVba** · 04/11/2020, 14h51

Bonjour vanagreg,
Je sais que tu as hésité

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

**vanagreg** · 04/11/2020, 15h06

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

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.

**QuestVba** · 06/11/2020, 11h08

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.

**vanagreg** · 06/11/2020, 11h48

Bonjour,

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

**McM** · 06/11/2020, 11h52

Bonjour QuestVBA,

Voici un exemple.

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

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

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

**Waldar** · 06/11/2020, 19h08

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

Code :

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

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

**vanagreg** · 07/11/2020, 10h29

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

**QuestVba** · 09/11/2020, 06h38

Bonjour à tous,

Voici le xml qui se trouve dans la DB.

Code XML :

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

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

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

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

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

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 ?

**McM** · 10/11/2020, 11h36

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

Code :

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

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 :

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

**QuestVba** · 10/11/2020, 15h17

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 :

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

**QuestVba** · 12/11/2020, 15h06

Voilà au final sans le node dans le Xpath :

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

**McM** · 12/11/2020, 15h15

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

**QuestVba** · 13/11/2020, 06h27

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

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

SQL Oracle

Discussions similaires

Partager

Partager