Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
XML Parsing Oracle 11g

Hi
I would like to get the ouput like below from the given XML data. Can you please help me to frame the SQL query
Output
classWise_BookedCount | classWise_CheckinCount |
---|---|
000F/007J/174Y | 000F/007J/172Y |
XML
<root>
<bookedCount>
<classWiseConfiguration load="0" classCode="F"/>
<classWiseConfiguration load="7" classCode="J"/>
<classWiseConfiguration load="174" classCode="Y"/>
</bookedCount>
<checkedInCount>
<classWiseConfiguration load="0" classCode="F"/>
<classWiseConfiguration load="7" classCode="J"/>
<classWiseConfiguration load="172" classCode="Y"/>
</checkedInCount>
</root>
Thanks,
Venkataraman L
Answers
-
SQL> ed
Wrote file afiedt.buf 1 with t as (select xmltype('<root>
2 <bookedCount>
3 <classWiseConfiguration load="0" classCode="F"/>
4 <classWiseConfiguration load="7" classCode="J"/>
5 <classWiseConfiguration load="174" classCode="Y"/>
6 </bookedCount>
7 <checkedInCount>
8 <classWiseConfiguration load="0" classCode="F"/>
9 <classWiseConfiguration load="7" classCode="J"/>
10 <classWiseConfiguration load="172" classCode="Y"/>
11 </checkedInCount>
12 </root>') as xml from dual)
13 --
14 -- end of test data
15 --
16 select to_char(bc_l_1,'fm000')||bc_c_1||'/'||
17 to_char(bc_l_2,'fm000')||bc_c_2||'/'||
18 to_char(bc_l_3,'fm000')||bc_c_3 as classWise_BookedCount
19 ,to_char(cc_l_1,'fm000')||cc_c_1||'/'||
20 to_char(cc_l_2,'fm000')||cc_c_2||'/'||
21 to_char(cc_l_3,'fm000')||cc_c_3 as classWise_CheckedInCount
22 from t
23 ,xmltable('/root'
24 passing t.xml
25 columns bc_l_1 number path './bookedCount/classWiseConfiguration[1]/@load'
26 ,bc_l_2 number path './bookedCount/classWiseConfiguration[2]/@load'
27 ,bc_l_3 number path './bookedCount/classWiseConfiguration[3]/@load'
28 ,bc_c_1 char(1) path './bookedCount/classWiseConfiguration[1]/@classCode'
29 ,bc_c_2 char(1) path './bookedCount/classWiseConfiguration[2]/@classCode'
30 ,bc_c_3 char(1) path './bookedCount/classWiseConfiguration[3]/@classCode'
31 ,cc_l_1 number path './checkedInCount/classWiseConfiguration[1]/@load'
32 ,cc_l_2 number path './checkedInCount/classWiseConfiguration[2]/@load'
33 ,cc_l_3 number path './checkedInCount/classWiseConfiguration[3]/@load'
34 ,cc_c_1 char(1) path './checkedInCount/classWiseConfiguration[1]/@classCode'
35 ,cc_c_2 char(1) path './checkedInCount/classWiseConfiguration[2]/@classCode'
36 ,cc_c_3 char(1) path './checkedInCount/classWiseConfiguration[3]/@classCode'
37* ) x
SQL> / CLASSWISE_BOOKEDC CLASSWISE_CHECKED
----------------- -----------------
000F/007J/174Y 000F/007J/172Y -
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SELECT *
2 FROM (SELECT a.node_name,
3 LPAD (b.load, 3, '0') || b.class_code class_code
4 FROM table_name tn,
5 XMLTABLE (
6 'root/(bookedCount|checkedInCount)'
7 PASSING
8 tn.xml
9 COLUMNS
10 node_name VARCHAR2(4000) path 'name()',
11 xml xmltype path '.') a,
12 XMLTABLE (
13 '*/classWiseConfiguration'
14 PASSING
15 a.xml
16 COLUMNS
17 load VARCHAR2(30) path '@load',
18 class_code VARCHAR2(30) path '@classCode') b)
19 PIVOT (
20 LISTAGG (
21 class_code, '/')
22 WITHIN GROUP (
23 ORDER BY class_code)
24 FOR node_name IN (
25 'bookedCount' as "classWise_BookedCount",
26 'checkedInCount' as "classWise_CheckinCount"));classWise_BookedCount classWise_CheckinCount
------------------------------ ------------------------------
000F/007J/174Y 000F/007J/172YSQL>
-
And a pure XQuery solution :
select x.* from t , xmltable( 'declare function local:gencode($e as element()) as xs:string* { string-join( for $i in $e/classWiseConfiguration let $str := concat($i/@load, $i/@classCode) return concat(substring("0000", string-length($str)+1), $str) , "/" ) }; (::) element r { element bookedCount {local:gencode(/root/bookedCount)} , element checkedInCount {local:gencode(/root/checkedInCount)} }' passing t.xml columns classWise_BookedCount varchar2(4000) path 'bookedCount' , classWise_CheckedInCount varchar2(4000) path 'checkedInCount' ) x ;
-
Nice, Odie, but would you typically choose 100% xquery over other options, and would that choice be affected by RETURNING SEQUENCE BY REF in 12c?
-
padders wrote: Nice, Odie, but would you typically choose 100% xquery over other options, and would that choice be affected by RETURNING SEQUENCE BY REF in 12c?
I would choose pure a XQuery solution when the XQ expression only involves core functions, so that the CBO may optimize it using kernel's SQL functions.
The storage type of the input XML document matters too. Generally speaking, a functional evaluation via a call to the XQuery VM is more expensive compared to a streaming-based evaluation over binary XML. However, thanks to the new 12c XQ engine, some evaluations are now very fast even on transient XMLType instances.
Back to the current example, I have to admit if I were to implement it in real life, I'd probably use a mix of XQ and SQL (to perform the lpad and the aggregation parts).
I don't know whether RETURNING SEQUENCE BY REF could be useful in this situation.
I would only use this option when I want to access nodes in the context of the main XQ expression, from the COLUMNS clause.