This discussion is archived
4 Replies Latest reply: Nov 5, 2012 5:54 AM by 932713 RSS

[INQUIRY]: XML with incrementing Elements

932713 Newbie
Currently Being Moderated
Hi,

I encountered a scenario where I have to parse an XML File with incrementing elements. I've tried to use a variable using PL/SQL but it is failing due to missing columns, however when I use a hardcode value it is working. The SQL Statement is below.
VARIABLE ELEMNUM NUMBER
ELEMNUM := 0

SELECT x.*
      FROM CSB_STG_SKY_INVEXTRACT_XMLTYPE y,
    --XMLTable('invoice/CLOUD_FLD_PARTS[@elem="' || ELEMNUM || '"]'
    XMLTable('invoice/CLOUD_FLD_PARTS[@elem="0"]'
                    PASSING y.C2
                    COLUMNS
                    SHIPPING_METHOD           VARCHAR (1)    PATH  'CLOUD_FLD_HEADER_INFO/PIN_FLD_1/CLOUD_FLD_1'
                   ) x ;
The @elem value could increment from 0 to N.. that is why I tried to use a variable so that I could increment that variable, but it failed.

Please help

Edited by: 929710 on Nov 5, 2012 5:28 AM
  • 1. Re: [INQUIRY]: XML with incrementing Elements
    odie_63 Guru
    Currently Being Moderated
    Please help
    OK.

    Give your database version, and a sample XML document + expected output please.
  • 2. Re: [INQUIRY]: XML with incrementing Elements
    932713 Newbie
    Currently Being Moderated
    hi odie

    the database version is 11g(11.2.0.3.0)

    Sample XML
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <invoice>
    
         <CLOUD_FLD_PARTS elem="0">
              <CLOUD_FLD_HEADER_INFO>
                   <PIN_FLD_1>
                        <CLOUD_FLD_1>W</CLOUD_FLD_1>
                   </PIN_FLD_1>
              </CLOUD_FLD_HEADER_INFO>
         </CLOUD_FLD_PARTS>
         <CLOUD_FLD_PARTS elem="1">
              <CLOUD_FLD_HEADER_INFO>
                   <PIN_FLD_1>
                        <CLOUD_FLD_1>Y</CLOUD_FLD_1>
                   </PIN_FLD_1>
              </CLOUD_FLD_HEADER_INFO>
         </CLOUD_FLD_PARTS>
         <CLOUD_FLD_PARTS elem="2">
              <CLOUD_FLD_HEADER_INFO>
                   <PIN_FLD_1>
                        <CLOUD_FLD_1>Z</CLOUD_FLD_1>
                   </PIN_FLD_1>
              </CLOUD_FLD_HEADER_INFO>
         </CLOUD_FLD_PARTS>
    </invoice>
    expected output
    SHIPPING_METHOD
    ------------------------
    W
    Y
    Z
  • 3. Re: [INQUIRY]: XML with incrementing Elements
    odie_63 Guru
    Currently Being Moderated
    Thanks.

    So what's wrong with :
    SQL> SELECT x.*
      2  FROM csb_stg_sky_invextract_xmltype y
      3     , XMLTable('invoice/CLOUD_FLD_PARTS'
      4         PASSING y.C2
      5         COLUMNS
      6           SHIPPING_METHOD  VARCHAR (1) PATH 'CLOUD_FLD_HEADER_INFO/PIN_FLD_1/CLOUD_FLD_1'
      7       ) x
      8  ;
     
    SHIPPING_METHOD
    ---------------
    W
    Y
    Z
     
    ?
  • 4. Re: [INQUIRY]: XML with incrementing Elements
    932713 Newbie
    Currently Being Moderated
    thanks!! I think the problem is I explicitly included the element value. I spent hours of looking on ways to make it work, adding stuff.. when all I need to do is remove stuff.

    you saved me a lot of hours with your help! it is greatly appreciated

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points