SQL & PL/SQL

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 options

SQL> 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/172Y

SQL>

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
;

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.