Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

XML Parsing using PLSQL

848964Sep 10 2013 — edited Sep 10 2013

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 8 2013
Added on Sep 10 2013
3 comments
1,978 views