4 Replies Latest reply: Nov 5, 2012 7:54 AM by 932713 RSS

    [INQUIRY]: XML with incrementing Elements

    932713
      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
          Please help
          OK.

          Give your database version, and a sample XML document + expected output please.
          • 2. Re: [INQUIRY]: XML with incrementing Elements
            932713
            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
              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
                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