Forum Stats

  • 3,874,838 Users
  • 2,266,780 Discussions
  • 7,911,975 Comments

Discussions

Categories

Getting XMLRecord value by position

3679117
3679117 Member Posts: 17
edited Apr 15, 2018 2:16PM in General XML

Hello. I've got the following XML Record:

<row id="123456">

   <c1>USD</c1>

   <c2>AMOUNTS</c2>

   <c2 m="2">AMOUNT1</c2>

   <c2 m="3">AMOUNT2</c2>

   <c2 m="4">AMOUNT3</c2>

   <c2 m="5">AMOUNT4</c2>

   <c3 m="6" />

   <c4 m="7" />

   <c5>50000000</c5>

   <c5 m="2">2000000</c5>

   <c5 m="3">15000000</c5>

   <c5 m="4">2000000</c5>

   <c5 m="5">538773.07</c5>

    ....

    ....

</row>

I would like to know how is it possible for me to join the AMOUNTX to the respective value?

For e.g, AMOUNT1 should match to 2000000, AMOUNT2 should match to 15000000

I need to "play" with the value of m to actually do the mapping since not all XML Records have a fixed position for each AMOUNT.

Can anyone please help with doing such mapping?

· Share on TwitterShare on Facebook

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    edited Mar 30, 2018 7:09AM Answer ✓

    select amount, amtval

    from example,

        xmltable(

            'for $r in /row

                for $c2 in $r/c2

                    let $m2 := $c2/@m

                    let $c5 := $r/c5[@m=$m2]

                    where exists($c5)

                        return <amt> {$c2} {$c5} </amt>'

        passing yourdata

        columns 

            amount varchar2(10) path 'c2',

            amtval number(10,2) path 'number(c5)'

        )

    where amount = 'AMOUNT3' 

    or

    select amount, amtval

    from example,

        xmltable(

            'for $r in /row

                for $c2 in $r/c2[.="AMOUNT3"]

                    let $m2 := $c2/@m

                    let $c5 := $r/c5[@m=$m2]

                    where exists($c5)

                        return <amt> {$c2} {$c5} </amt>'

        passing yourdata

        columns 

            amount varchar2(10) path 'c2',

            amtval number(10,2) path 'number(c5)'

        )

    · Share on TwitterShare on Facebook

Answers

  • cormaco
    cormaco Member Posts: 2,041 Silver Crown
    edited Mar 27, 2018 7:51AM

    This is a very weird dataformat, however I was able to do this:

    with example(yourdata) as (select xmltype(    '<row id="123456">       <c1>USD</c1>       <c2>AMOUNTS</c2>       <c2 m="2">AMOUNT1</c2>       <c2 m="3">AMOUNT2</c2>       <c2 m="4">AMOUNT3</c2>       <c2 m="5">AMOUNT4</c2>       <c3 m="6" />       <c4 m="7" />       <c5>50000000</c5>       <c5 m="2">2000000</c5>       <c5 m="3">15000000</c5>       <c5 m="4">2000000</c5>       <c5 m="5">538773.07</c5>    </row>') from dual)select amount,amtval from example,    xmltable(        'for $r in /row            for $c2 in $r/c2                let $m2 := $c2/@m                let $c5 := $r/c5[@m=$m2]                where exists($c5)                    return <amt> {$c2} {$c5} </amt>'    passing yourdata    columns         amount varchar2(10) path 'c2',        amtval number(10,2) path 'number(c5)'    )         

    Output:

    AMOUNT   AMTVAL

    ---------- ----------

    AMOUNT1 2000000
    AMOUNT215000000
    AMOUNT3 2000000
    AMOUNT4538773,07
    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    edited Mar 27, 2018 8:25AM

    Another approach, not as succinct as Cormaco's XQuery approach, but should outperform it.

    with unpivoted(n, rn, val) as (

      select 1, m, val

      from example,

        xmltable('/row/c2[@m]'

        passing yourdata

        columns

          m   integer      path '@m',

          val varchar2(20) path '.'

        )

      union all

      select 2, m, val

      from example,

        xmltable('/row/c5[@m]'

        passing yourdata

        columns

          m   integer      path '@m',

          val varchar2(20) path '.'

        )

    )

    select rn, amount, to_number(value) value

    from unpivoted

    pivot (

      min(val)

      for n in (1 as AMOUNT, 2 as VALUE)

    )

    · Share on TwitterShare on Facebook
  • 3679117
    3679117 Member Posts: 17
    edited Mar 29, 2018 2:08AM

    Thank you for the replies. I will try and get back to you.

    · Share on TwitterShare on Facebook
  • 3679117
    3679117 Member Posts: 17
    edited Mar 30, 2018 7:00AM

    How do I modify the code if I want to lookup the value for AMOUNT3 only?

    i.e if I want to pass parameters to lookup the corresponding amount

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    edited Mar 30, 2018 7:09AM Answer ✓

    select amount, amtval

    from example,

        xmltable(

            'for $r in /row

                for $c2 in $r/c2

                    let $m2 := $c2/@m

                    let $c5 := $r/c5[@m=$m2]

                    where exists($c5)

                        return <amt> {$c2} {$c5} </amt>'

        passing yourdata

        columns 

            amount varchar2(10) path 'c2',

            amtval number(10,2) path 'number(c5)'

        )

    where amount = 'AMOUNT3' 

    or

    select amount, amtval

    from example,

        xmltable(

            'for $r in /row

                for $c2 in $r/c2[.="AMOUNT3"]

                    let $m2 := $c2/@m

                    let $c5 := $r/c5[@m=$m2]

                    where exists($c5)

                        return <amt> {$c2} {$c5} </amt>'

        passing yourdata

        columns 

            amount varchar2(10) path 'c2',

            amtval number(10,2) path 'number(c5)'

        )

    · Share on TwitterShare on Facebook
  • 3679117
    3679117 Member Posts: 17
    edited Apr 8, 2018 3:03AM

    I'm trying to use the code in a View in order to return the sum of amount in a column. However, I want to do a JOIN so that I can pass a parameter from the main query to the subquery as follows.

       

    create view    view_testasselecta.column1,a.column2,(select sum(amtval)from example,    xmltable(        'for $r in /row            for $c2 in $r/c2                let $m2 := $c2/@m                let $c5 := $r/c5[@m=$m2]                where exists($c5)                    return <amt> {$c2} {$c5} </amt>'    passing yourdata    columns         amount varchar2(10) path 'c2',        amtval number(10,2) path 'number(c5)'    )where amount = a.column2),a.column3fromtest_table a;

    I am getting invalid identifier error when trying to do so.

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,802 Blue Diamond
    edited Apr 8, 2018 1:57PM

    Start a new question, this is a different problem.

    · Share on TwitterShare on Facebook
  • 3679117
    3679117 Member Posts: 17
    edited Apr 13, 2018 3:17AM

    Hello. How can I return the attribute id from the element row as well?

    <row id="123456">

       <c1>USD</c1>

       <c2>AMOUNTS</c2>

       <c2 m="2">AMOUNT1</c2>

    · Share on TwitterShare on Facebook
  • cormaco
    cormaco Member Posts: 2,041 Silver Crown
    edited Apr 13, 2018 7:21AM 
    3679117 schrieb:Hello. How can I return the attribute id from the element row as well?<row id="123456"> <c1>USD</c1> <c2>AMOUNTS</c2> <c2 m="2">AMOUNT1</c2>

    Like this:

    SELECT row_id,amount,amtval FROM example,      XMLTABLE(          'for $r in /row            let $rowid := $r/@id            for $c2 in $r/c2                  let $m2 := $c2/@m                  let $c5 := $r/c5[@m=$m2]                  where exists($c5)                      return <amt> {$rowid} {$c2} {$c5} </amt>'      PASSING yourdata      COLUMNS        row_id number(10)   PATH '@id',        amount varchar2(10) path 'c2',          amtval number(10,2) path 'number(c5)'      )           

    Output:

        ROW_ID AMOUNT         AMTVAL

    ---------- ---------- ----------

        123456 AMOUNT1       2000000

        123456 AMOUNT2      15000000

        123456 AMOUNT3       2000000

        123456 AMOUNT4     538773,07

    · Share on TwitterShare on Facebook
  • 3679117
    3679117 Member Posts: 17
    edited Apr 15, 2018 2:16PM

    Hello. Thanks, it worked. I'm now trying to filter out the results by the AMOUNT column, but I'm getting the following error:

    ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

    19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"

    *Cause:    The XQuery sequence passed in had more than one item.

    *Action:  Correct the XQuery expression to return a single item sequence.

    Here is the actual code as I am trying to use:

    with example(yourdata) as (select a.XMLRECORD from "FBNK_EB_CONTRACT_BA001" a)select row_id,amount,amtval from example,    xmltable(        'for $r in /row            let $rowid := $r/@id            for $c2 in $r/c2                let $m2 := $c2/@m                let $c5 := $r/c5[@m=$m2]                where exists($c5)                    return <amt> {$rowid} {$c2} {$c5} </amt>'    passing yourdata    columns        row_id varchar2(30)  PATH '@id',        amount varchar2(30) path 'c2',        amtval varchar2(30) path 'c5'    ) where amount IN ('CURACCOUNT','DUEACCOUNT')
    · Share on TwitterShare on Facebook
This discussion has been closed.