Extraire XML via procedure

Version imprimable

Bonjour
J’ai besoin de pouvoir extraire les informations d’un XML (pour l’instant j’utilise un DBMS)
Dans l’exemple suivant je tente de faire um DBMS des valeurs de mon XML, le problème est que comme j’ai plusieur foi la tag <TICKET_NO> Oracle me retourne une erreur(ORA-19025: EXTRACTVALUE returns value of only one node).

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
 
DECLARE
   --
   retval xmltype := xmltype('<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <env:Header/>
  <env:Body>
  <TICKETCreateRespMsg xmlns="http://www.testurl.com/webservices/">
  <body xmlns="">
  <CreateTicket>
  <TICKET_NO>A-32758628</TICKET_NO>
  <TICKET_NO>C-32758628</TICKET_NO>
  <TICKET_NO>B-32758628</TICKET_NO>  
  <SUMMARY>Test1</SUMMARY>
  <ERROR_MSG>Test2</ERROR_MSG>
  </CreateTicket>
  </body>
  </TICKETCreateRespMsg>
  </env:Body>
  </env:Envelope>');
 
    v_ticket_no VARCHAR2(30);
    v_summary   VARCHAR2(30);
    v_error_msg VARCHAR2(30);
 
    v_ns_map    VARCHAR2(200) := 'xmlns:env="http://schemas.xmlsoap.org/soap/envelope/", xmlns:ns1="http://www.testurl.com/webservices/"';
    v_xpath     VARCHAR2(200) := '/env:Envelope/env:Body/ns1:TICKETCreateRespMsg/body/CreateTicket';
  --  
  BEGIN
  --
   FOR I IN (SELECT extractValue(retval, v_xpath || '/TICKET_NO', v_ns_map) TICKET_NO,
                    extractValue(retval, v_xpath || '/SUMMARY', v_ns_map) SUMMARY ,
                    extractValue(retval, v_xpath || '/ERROR_MSG', v_ns_map)ERROR_MSG
             --INTO v_ticket_no, v_summary, v_error_msg
               FROM dual) LOOP
   --           
   dbms_output.put_line('TICKET_NO = '||i.TICKET_NO);
   dbms_output.put_line('SUMMARY = '|| i.summary);
   dbms_output.put_line('ERROR_MSG = '|| i.error_msg);
  --  
  END LOOP;
--  
END;

Avez-vous une idée de comment faire pour extraire toutes les valeurs de mon XML?

31/05/2016, 09h40
mnitu

Utilisez XmlTable. Cherchez sur ce forum, j'ai posté à plusieurs reprises des exemples.

Probablement quelque chose comme l'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
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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2     --
  3    retval xmltype := xmltype('<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  4    <env:Header/>
  5    <env:Body>
  6    <TICKETCreateRespMsg xmlns="http://www.testurl.com/webservices/">
  7    <body xmlns="">
  8    <CreateTicket>
  9    <TICKET_NO>A-32758628</TICKET_NO>
 10    <TICKET_NO>C-32758628</TICKET_NO>
 11    <TICKET_NO>B-32758628</TICKET_NO>
 12    <SUMMARY>Test1</SUMMARY>
 13    <ERROR_MSG>Test2</ERROR_MSG>
 14    </CreateTicket>
 15    </body>
 16    </TICKETCreateRespMsg>
 17    </env:Body>
 18    </env:Envelope>');
 19    --
 20    BEGIN
 21    --
 22     FOR I IN (
 23     Select b.Ticket as TICKET_NO,
 24              a.Summary,
 25              a.error_msg
 26         From XmlTable(Xmlnamespaces('http://www.testurl.com/webservices/' as "ns1", 'http://schemas.xmlsoap.org/soap/envelope/' As "env"),
 27                       'for $i in /env:Envelope/env:Body/ns1:TICKETCreateRespMsg/body/CreateTicket
 28                        return $i
 29                  '
 30                  passing retval
 31                  Columns Summary     Varchar2(30)  Path 'SUMMARY',
 32                          error_msg   Varchar2(30)  Path 'ERROR_MSG',
 33                          Tickets     XmlType       Path 'TICKET_NO'
 34                 ) a,
 35              XmlTable ('for $i in TICKET_NO
 36                         return $i
 37                        '
 38                  passing a.Tickets
 39                  Columns Ticket     Varchar2(30)  Path '/TICKET_NO'
 40                       ) b
 41     ) LOOP
 42     --
 43     dbms_output.put_line('TICKET_NO = '||i.TICKET_NO);
 44     dbms_output.put_line('SUMMARY = '|| i.summary);
 45     dbms_output.put_line('ERROR_MSG = '|| i.error_msg);
 46    --
 47    END LOOP;
 48  --
 49  END;
 50  /
TICKET_NO = A-32758628
SUMMARY = Test1
ERROR_MSG = Test2
TICKET_NO = C-32758628
SUMMARY = Test1
ERROR_MSG = Test2
TICKET_NO = B-32758628
SUMMARY = Test1
ERROR_MSG = Test2
PL/SQL procedure successfully completed
 
SQL>