Forum Stats

  • 3,826,405 Users
  • 2,260,641 Discussions


XML Parsing Oracle 11g

venkatraman.L Member Posts: 196
edited Sep 10, 2014 10:07AM in SQL & PL/SQL


I would like to get the ouput like below from the given XML data. Can you please help me to frame the SQL query






<classWiseConfiguration load="0" classCode="F"/>

<classWiseConfiguration load="7" classCode="J"/>

<classWiseConfiguration load="174" classCode="Y"/>



<classWiseConfiguration load="0" classCode="F"/>

<classWiseConfiguration load="7" classCode="J"/>

<classWiseConfiguration load="172" classCode="Y"/>




Venkataraman L



  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    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
    ----------------- -----------------
    000F/007J/174Y    000F/007J/172Y
  • padders
    padders Member Posts: 1,074 Silver Trophy

    Oracle Database 11g Enterprise Edition Release - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

      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


  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy

    And a pure XQuery solution :

    select x.* 
    from t
       , xmltable(
           'declare function local:gencode($e as element()) as xs:string*
                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
    padders Member Posts: 1,074 Silver Trophy

    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?

  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy
    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.

This discussion has been closed.