This discussion is archived
3 Replies Latest reply: Sep 10, 2013 1:49 PM by Jason_(A_Non) RSS

XML Parsing using PLSQL

848964 Newbie
Currently Being Moderated

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

  • 1. Re: XML Parsing using PLSQL
    odie_63 Guru
    Currently Being Moderated

    This should solve your problem :

    select *

    from xmltable(

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

         , '/ns1:Forecast/ns1:row'

           passing p_xml_source

           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'

         ) ;

    The main XQuery expression '/ns1:Forecast/ns1:row' defines the sequence of items that will be the relational rows of the virtual table.

    Then, each PATH expression in the COLUMNS clause is evaluated relative to the context item from the sequence.

     

    I suppose p_xml_source is of XMLType datatype, right? Why are you serializing it to string and convert it again to XMLType? Just use the parameter directly.

  • 2. Re: XML Parsing using PLSQL
    848964 Newbie
    Currently Being Moderated

    Thanks for the response. I will try this out.

     

    Yes p_xml_source is of XMLType

     

    What do you mean by serializing. Do you mean to say, I should directly use as below:

     

           columns PM_PRODUCT_CODE          path 'ns1:PM_PRODUCT_CODE',

                   PM_EVENT_REFERENCE       path 'ns1:PM_EVENT_REFERENCE',

                   TASK_NUMBER              path 'ns1:TASK_NUMBER',

                   EVENT_TYPE               path 'ns1:EVENT_TYPE',

                   DESCRIPTION              path 'ns1:DESCRIPTION',

                   COMPLETION_DATE          path 'ns1:COMPLETION_DATE',

                   PROJECT_NUMBER           path 'ns1:PROJECT_NUMBER',

                   ORGANIZATION_NAME        path 'ns1:ORGANIZATION_NAME',

                   BILL_TRANS_CURRENCY_CODE path 'ns1:BILL_TRANS_CURRENCY_CODE',

                   BILL_TRANS_BILL_AMOUNT   path 'ns1:BILL_TRANS_BILL_AMOUNT',

                   BILL_TRANS_REV_AMOUNT    path 'ns1:BILL_TRANS_REV_AMOUNT'

     

    Thanks & Regards,

    Ad

  • 3. Re: XML Parsing using PLSQL
    Jason_(A_Non) Expert
    Currently Being Moderated

    Odie was commenting on the fact that this line

    > xmltype( p_xml_source.getstringval())

    performs the following datatype operations

    XMLType -> VARCHAR2 -> XMLType

    So why even serialize (convert the XMLType to text in this case) it in the first place and convert it right back if p_xml_source is already an XMLType, which is what XMLTable is expecting.

     

    For what you referenced, he simply removed unnecessary XPath operators since those nodes are direct children of the ns1:row node.  It improves performance slightly since the engine knows where the node will reside in the XML and does not have to search the fragment for it.

Legend

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