Forum Stats

  • 3,817,233 Users
  • 2,259,294 Discussions
  • 7,893,707 Comments

Discussions

Extract Value from XML in Pl-Sql

T J
T J Member Posts: 105 Red Ribbon
edited Jan 18, 2018 4:11AM in SQL & PL/SQL

Hi I'm making an anonymous block to test extract function on my XML.

This is I,m using

DECLARE       i_xpayload                   CLOB;       l_result_success             VARCHAR2(50);          BEGIN   DBMS_OUTPUT.PUT_LINE ('hi');   i_xpayload := XMLTYPE('<Confirmation revision="001">    <App>        <Sender>            <Component>A</Component>            <Confirm>Never</Confirm>        </Sender>        <CreationDateTime>2017-06-13</CreationDateTime>    </App></Confirmation>').getclobval();    l_result_success := i_xpayload.EXTRACT ('/Confirmation/App/Sender/Confirm//text()').getstringval ();    DBMS_OUTPUT.PUT_LINE ('l_result_success ' || l_result_success);END;

The procedure is completed successfully when I'm not using

    l_result_success := i_xpayload.EXTRACT ('/Confirmation/App/Sender/Confirm//text()').getstringval ();

    DBMS_OUTPUT.PUT_LINE ('l_result_success ' || l_result_success);

But when I do it is giving me error

ORA-06550: line 19, column 36:

PLS-00487: Invalid reference to variable 'I_XPAYLOAD'

ORA-06550: line 19, column 5:

PL/SQL: Statement ignored

Can someone tell me please what I'am doing wrong.

TIA

Tagged:
T Jmanaged BEAN

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond
    edited Jan 18, 2018 3:25AM Answer ✓

    Here's an example...

    SQL> declare
      2    i_xpayload      xmltype;
      3    l_result_success varchar2(50);
      4    cursor getXml is
      5      select x.*
      6      from  xmltable('/Confirmation'
      7                      passing i_xpayload
      8                      columns revision  varchar2(3)  path './@revision'
      9                            ,component  varchar2(1)  path './App/Sender/Component'
    10                            ,confirm    varchar2(5)  path './App/Sender/Confirm'
    11                            ,creationdt varchar2(10) path './App/CreationDateTime'
    12                    ) x;
    13  begin
    14    i_xpayload := XMLTYPE('<Confirmation revision="001">
    15    <App>
    16      <Sender>
    17        <Component>A</Component>
    18        <Confirm>Never</Confirm>
    19      </Sender>
    20      <CreationDateTime>2017-06-13</CreationDateTime>
    21    </App>
    22  </Confirmation>');
    23    for x in getXML
    24    loop
    25      dbms_output.put_line('Revision: '||x.revision);
    26      dbms_output.put_line('Component: '||x.component);
    27      dbms_output.put_line('Confirm: '||x.confirm);
    28      dbms_output.put_line('Creation Date/Time: '||to_char(to_date(x.creationdt,'YYYY-MM-DD'),'DD/MM/YYYY HH24:MI:SS'));
    29    end loop;
    30  end;
    31  /
    Revision: 001
    Component: A
    Confirm: Never
    Creation Date/Time: 13/06/2017 00:00:00

    Typically, your data will be coming form the database anyway, so it's better to use SQL for the extraction of the data from XML.

    managed BEAN

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond
    edited Jan 18, 2018 3:17AM

    you've defined I_xpayload as a CLOB datatype, yet try and put an XMLTYPE datatype in to it.

    For starters it should be declared as XMLTYPE.

    Next, the use of EXTRACT and getStringVal() etc. are now deprecated coding.

    For processing XML, you would be better using SQL's XMLTABLE functionality.... I'll see if I can knock up a quick example...

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond
    edited Jan 18, 2018 3:25AM Answer ✓

    Here's an example...

    SQL> declare
      2    i_xpayload      xmltype;
      3    l_result_success varchar2(50);
      4    cursor getXml is
      5      select x.*
      6      from  xmltable('/Confirmation'
      7                      passing i_xpayload
      8                      columns revision  varchar2(3)  path './@revision'
      9                            ,component  varchar2(1)  path './App/Sender/Component'
    10                            ,confirm    varchar2(5)  path './App/Sender/Confirm'
    11                            ,creationdt varchar2(10) path './App/CreationDateTime'
    12                    ) x;
    13  begin
    14    i_xpayload := XMLTYPE('<Confirmation revision="001">
    15    <App>
    16      <Sender>
    17        <Component>A</Component>
    18        <Confirm>Never</Confirm>
    19      </Sender>
    20      <CreationDateTime>2017-06-13</CreationDateTime>
    21    </App>
    22  </Confirmation>');
    23    for x in getXML
    24    loop
    25      dbms_output.put_line('Revision: '||x.revision);
    26      dbms_output.put_line('Component: '||x.component);
    27      dbms_output.put_line('Confirm: '||x.confirm);
    28      dbms_output.put_line('Creation Date/Time: '||to_char(to_date(x.creationdt,'YYYY-MM-DD'),'DD/MM/YYYY HH24:MI:SS'));
    29    end loop;
    30  end;
    31  /
    Revision: 001
    Component: A
    Confirm: Never
    Creation Date/Time: 13/06/2017 00:00:00

    Typically, your data will be coming form the database anyway, so it's better to use SQL for the extraction of the data from XML.

    managed BEAN
  • cormaco
    cormaco Member Posts: 1,909 Silver Crown
    edited Jan 18, 2018 3:30AM

    Hi BluShadow,

    please post your code without line numbers, the code can't be tried out like this without manually removing them.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond
    edited Jan 18, 2018 3:43AM

    Why does it need to be "tried", when I've already tried it?

    It's not that much work to remove the line numbers (seems like some people have lost the ability to use a keyboard!)

    declare

      i_xpayload       xmltype;

      l_result_success varchar2(50);

      cursor getXml is

        select x.*

        from   xmltable('/Confirmation'

                        passing i_xpayload

                        columns revision   varchar2(3)  path './@revision'

                               ,component  varchar2(1)  path './App/Sender/Component'

                               ,confirm    varchar2(5)  path './App/Sender/Confirm'

                               ,creationdt varchar2(10) path './App/CreationDateTime'

                       ) x;

    begin

      i_xpayload := XMLTYPE('<Confirmation revision="001">

      <App>

        <Sender>

          <Component>A</Component>

          <Confirm>Never</Confirm>

        </Sender>

        <CreationDateTime>2017-06-13</CreationDateTime>

      </App>

    </Confirmation>');

      for x in getXML

      loop

        dbms_output.put_line('Revision: '||x.revision);

        dbms_output.put_line('Component: '||x.component);

        dbms_output.put_line('Confirm: '||x.confirm);

        dbms_output.put_line('Creation Date/Time: '||to_char(to_date(x.creationdt,'YYYY-MM-DD'),'DD/MM/YYYY HH24:MI:SS'));

      end loop;

    end;

    /

  • T J
    T J Member Posts: 105 Red Ribbon
    edited Jan 18, 2018 3:46AM

    Thanks @BluShadow for your answer

    @BluShadow Do existnode also work like this or I can simply use it like this

    i_xpayload.EXISTSNODE ('/Confirmation/App/Sender/Confirm')  = 1

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond
    edited Jan 18, 2018 4:06AM

    Well, take the Confirm data out of the XML and see what happens...

    SQL> declare
      2    i_xpayload      xmltype;
      3    l_result_success varchar2(50);
      4    cursor getXml is
      5      select x.*
      6      from  xmltable('/Confirmation'
      7                      passing i_xpayload
      8                      columns revision  varchar2(3)  path './@revision'
      9                            ,component  varchar2(1)  path './App/Sender/Component'
    10                            ,confirm    varchar2(5)  path './App/Sender/Confirm'
    11                            ,creationdt varchar2(10) path './App/CreationDateTime'
    12                    ) x;
    13  begin
    14    i_xpayload := XMLTYPE('<Confirmation revision="001">
    15    <App>
    16      <Sender>
    17        <Component>A</Component>
    18        <!-- <Confirm>Never</Confirm> -->
    19      </Sender>
    20      <CreationDateTime>2017-06-13</CreationDateTime>
    21    </App>
    22  </Confirmation>');
    23    for x in getXML
    24    loop
    25      dbms_output.put_line('Revision: '||x.revision);
    26      dbms_output.put_line('Component: '||x.component);
    27      dbms_output.put_line('Confirm: '||x.confirm);
    28      dbms_output.put_line('Creation Date/Time: '||to_char(to_date(x.creationdt,'YYYY-MM-DD'),'DD/MM/YYYY HH24:MI:SS'));
    29    end loop;
    30  end;
    31  /
    Revision: 001
    Component: A
    Confirm:
    Creation Date/Time: 13/06/2017 00:00:00

    The value of the resulting column from the query is null, so you can just test for NULL in the code.

    You could also test it in the SQL if you want to exclude the whole data if it doesn't contain a "confirm" value...

      4    cursor getXml is

      5      select x.*

      6      from   xmltable('/Confirmation'

      7                      passing i_xpayload

      8                      columns revision   varchar2(3)  path './@revision'

      9                             ,component  varchar2(1)  path './App/Sender/Component'

    10                             ,confirm    varchar2(5)  path './App/Sender/Confirm'

    11                             ,creationdt varchar2(10) path './App/CreationDateTime'

    12                     ) x

    13      where x.confirm is not null;

    Or you can do it in the Xpath expression...

      4    cursor getXml is  5      select x.*  6      from   xmltable('/Confirmation[boolean(./App/Sender/Confirm)]'  7                      passing i_xpayload  8                      columns revision   varchar2(3)  path './@revision'  9                             ,component  varchar2(1)  path './App/Sender/Component' 10                             ,confirm    varchar2(5)  path './App/Sender/Confirm' 11                             ,creationdt varchar2(10) path './App/CreationDateTime' 12                     ) x;

    Or, yes, you could use ExistsNode function...

      4    cursor getXml is  5      select x.*  6      from   xmltable('/Confirmation'  7                      passing i_xpayload  8                      columns revision   varchar2(3)  path './@revision'  9                             ,component  varchar2(1)  path './App/Sender/Component' 10                             ,confirm    varchar2(5)  path './App/Sender/Confirm' 11                             ,creationdt varchar2(10) path './App/CreationDateTime' 12                     ) x 13      where i_xpayload.existsNode('/Confirmation/App/Sender/Confirm') = 1;

    Personally I'd keep it as part of the XMLTABLE functionality or testing the result from the XMLTABLE as you're testing the values in the data of that 'table', rather than testing it independently.

    Edit: Corrected typo in Boolean check

    T JT J
  • T J
    T J Member Posts: 105 Red Ribbon
    edited Jan 18, 2018 4:11AM

    Thanks @BluShadow for your help

This discussion has been closed.