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
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> With Data As (
2 Select xmltype('<root>
3 <s id="s1">
4 <z code="A01" libelle="ESPACE A01">
5 <p debut="10/03/2014 00:15" fin="10/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
6 <p debut="11/03/2014 00:15" fin="11/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
7 <p debut="12/03/2014 00:15" fin="12/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
8 </z>
9 </s>
10 </root>') x_el from dual
11 )
12 Select a.S_Id, a.Z_Code, b.p_debut
13 From data t,
14 XmlTable ('/root'
15 passing t.x_el
16 COLUMNS S_Id Varchar2(10) Path 's/@id',
17 Z_Code Varchar2(10) Path 's/z/@code',
18 p_xel XmlType Path 's/z/p'
19 ) a,
20 XmlTable ('/p'
21 Passing a.p_xel
22 COLUMNS p_debut Varchar2(10) Path '@debut'
23 ) b
24 /
S_ID Z_CODE P_DEBUT
---------- ---------- ----------
s1 A01 10/03/2014
s1 A01 11/03/2014
s1 A01 12/03/2014
SQL> |
Partager