DevOps, CI/CD and Automation

Hi,

I am having an xml data as shown :

Here there are 2 rows which need to be processed based on loop.

'<xs:Forecast xmlns:xs="http://www.w3.org/2001/XMLSchema">

                         <xs:row>

                         <xs:PM_PRODUCT_CODE>OKE</xs:PM_PRODUCT_CODE>

                         <xs:PM_EVENT_REFERENCE>200106989</xs:PM_EVENT_REFERENCE>

                         <xs:TASK_NUMBER>RED11000</xs:TASK_NUMBER>   

                         <xs:EVENT_TYPE>Data Migration</xs:EVENT_TYPE>

                         <xs:DESCRIPTION>YIK Automated Billing Event1</xs:DESCRIPTION>

                         <xs:COMPLETION_DATE>31-AUG-2013</xs:COMPLETION_DATE>                        

                         <xs:PROJECT_NUMBER>200105</xs:PROJECT_NUMBER>

                         <xs:ORGANIZATION_NAME>AUIM - AU Retail Development - National</xs:ORGANIZATION_NAME>                        

                         <xs:BILL_TRANS_CURRENCY_CODE>AUD</xs:BILL_TRANS_CURRENCY_CODE>

                         <xs:BILL_TRANS_BILL_AMOUNT>4444</xs:BILL_TRANS_BILL_AMOUNT>

                         <xs:BILL_TRANS_REV_AMOUNT>0</xs:BILL_TRANS_REV_AMOUNT>

                         </xs:row>

                         <xs:row>

                         <xs:PM_PRODUCT_CODE>OKE</xs:PM_PRODUCT_CODE>

                         <xs:PM_EVENT_REFERENCE>200106987</xs:PM_EVENT_REFERENCE>

                         <xs:TASK_NUMBER>RED11000</xs:TASK_NUMBER>   

                         <xs:EVENT_TYPE>Data Migration</xs:EVENT_TYPE>

                         <xs:DESCRIPTION>YIK Automated Billing Event1</xs:DESCRIPTION>

                         <xs:COMPLETION_DATE>31-AUG-2013</xs:COMPLETION_DATE>                        

                         <xs:PROJECT_NUMBER>200105</xs:PROJECT_NUMBER>

                         <xs:ORGANIZATION_NAME>AUIM - AU Retail Development - National</xs:ORGANIZATION_NAME>                        

                         <xs:BILL_TRANS_CURRENCY_CODE>AUD</xs:BILL_TRANS_CURRENCY_CODE>

                         <xs:BILL_TRANS_BILL_AMOUNT>4444</xs:BILL_TRANS_BILL_AMOUNT>

                         <xs:BILL_TRANS_REV_AMOUNT>0</xs:BILL_TRANS_REV_AMOUNT>

                         </xs:row>

                        </xs:Forecast>'

Now, I was parsing as below :

select *

from xmltable(xmlnamespaces('http://www.w3.org/2001/XMLSchema' as "ns1"), '/ns1:Forecast'

            passing xmltype( p_xml_source.getstringval())

            columns PM_PRODUCT_CODE VARCHAR2(100) path '//ns1:PM_PRODUCT_CODE',

                    PM_EVENT_REFERENCE NUMBER path '//ns1:PM_EVENT_REFERENCE',

                    TASK_NUMBER VARCHAR2(100) path '//ns1:TASK_NUMBER',

                    EVENT_TYPE VARCHAR2(100) path '//ns1:EVENT_TYPE',

                    DESCRIPTION VARCHAR2(100) path '//ns1:DESCRIPTION',

                    COMPLETION_DATE VARCHAR2(100) path '//ns1:COMPLETION_DATE',

                    PROJECT_NUMBER VARCHAR2(100) path '//ns1:PROJECT_NUMBER',

                    ORGANIZATION_NAME VARCHAR2(100) path '//ns1:ORGANIZATION_NAME',

                    BILL_TRANS_CURRENCY_CODE VARCHAR2(100) path '//ns1:BILL_TRANS_CURRENCY_CODE',

                    BILL_TRANS_BILL_AMOUNT VARCHAR2(100) path '//ns1:BILL_TRANS_BILL_AMOUNT',

                    BILL_TRANS_REV_AMOUNT VARCHAR2(100) path '//ns1:BILL_TRANS_REV_AMOUNT'

                    )

This select is working fine for below where there is only one row but I need to make this work for the top most xml code where there are 2 rows splitted by a tag -named ROW

'<xs:Forecast xmlns:xs="http://www.w3.org/2001/XMLSchema">

                          <xs:PM_PRODUCT_CODE>OKE</xs:PM_PRODUCT_CODE>

                         <xs:PM_EVENT_REFERENCE>200106989</xs:PM_EVENT_REFERENCE>

                         <xs:TASK_NUMBER>RED11000</xs:TASK_NUMBER>   

                         <xs:EVENT_TYPE>Data Migration</xs:EVENT_TYPE>

                         <xs:DESCRIPTION>YIK Automated Billing Event1</xs:DESCRIPTION>

                         <xs:COMPLETION_DATE>31-AUG-2013</xs:COMPLETION_DATE>                        

                         <xs:PROJECT_NUMBER>200105</xs:PROJECT_NUMBER>

                         <xs:ORGANIZATION_NAME>AUIM - AU Retail Development - National</xs:ORGANIZATION_NAME>                        

                         <xs:BILL_TRANS_CURRENCY_CODE>AUD</xs:BILL_TRANS_CURRENCY_CODE>

                         <xs:BILL_TRANS_BILL_AMOUNT>4444</xs:BILL_TRANS_BILL_AMOUNT>

                         <xs:BILL_TRANS_REV_AMOUNT>0</xs:BILL_TRANS_REV_AMOUNT>

     </xs:Forecast>'

What I know some very minor changes needed in the portion - //ns1..............................

But I tried a few combinations but its still not working. I just want the above SQL to be modified to work for the top most XML data with two rows. Appreciate assiatnce on this as this is urgent and I am unable to proceed. For the time being I am reviewing some fundamentals of XML schema definition if that helps.

Thanks & Regards,

Ad