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