Forum Stats

  • 3,826,373 Users
  • 2,260,638 Discussions
  • 7,896,917 Comments

Discussions

Categories

XML Parsing Oracle 11g

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

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_BookedCountclassWise_CheckinCount
000F/007J/174Y000F/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

padders
· Share on TwitterShare on Facebook

Answers

  • 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
    SQL> /

CLASSWISE_BOOKEDC CLASSWISE_CHECKED
    ----------------- -----------------
    000F/007J/174Y    000F/007J/172Y
    venkatraman.L
    · Share on TwitterShare on Facebook
  • padders
    padders Member Posts: 1,074 Silver Trophy

    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>

    venkatraman.L
    · Share on TwitterShare on Facebook
  • 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*
        {
          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
    · Share on TwitterShare on Facebook
  • 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?

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
This discussion has been closed.