How to parse xml data from clob in oracle

Question

The xml is:

 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:abcs="http://www.avea.com.tr/om/ABCS_BSCS_ASSET_OPERATIONS" xmlns:ave="http://www.avea.com.tr/AveaFrameWork" xmlns:ebs="http://www.avea.com.tr/om/EBS_COMMITMENT_ASSET_OPERATIONS"><soapenv:Header/><soapenv:Body><ebs:EbsRetrieveProbableCommitmentPenaltiesResponse>
                <ave:ResponseHeader>
                    <ave:RequestId>
                        <!--Optional:-->
                        <ave:GUID>?</ave:GUID>
                        <!--Optional:-->
                        <ave:ListOfTransId>
                            <!--Zero or more repetitions:-->
                            <ave:TransId>
                                <ave:SystemName>?</ave:SystemName>
                                <ave:ExtendedValue>?</ave:ExtendedValue>
                            </ave:TransId>
                        </ave:ListOfTransId>
                    </ave:RequestId>
                    <!--Optional:-->
                    <ave:TimeStampDateTime>?</ave:TimeStampDateTime>
                    <!--Optional:-->
                    <ave:ElapsedTime>?</ave:ElapsedTime>
                </ave:ResponseHeader>
                <!--Optional:-->
                <abcs:ResponseBody>
                    <!--Optional:-->
                    <abcs:listOfCommitmentPenalty>
                        <!--Zero or more repetitions:-->
                        <abcs:commitmentPenalty>
                            <!--Optional:-->
                            <abcs:commitmentName>asddsfdsf dsfsdf</abcs:commitmentName>
                            <!--Optional:-->
                            <abcs:startDate>2014-11-12T16:06:05.664+02:00</abcs:startDate>
                            <!--Optional:-->
                            <abcs:endDate>2015-11-12T16:06:05.664+02:00</abcs:endDate>
                            <!--Optional:-->
                            <abcs:totalPeriod>365</abcs:totalPeriod>
                            <!--Optional:-->
                            <abcs:remainingPeriod>120</abcs:remainingPeriod>
                            <!--Optional:-->
                            <abcs:penaltyAmount>1200</abcs:penaltyAmount>
                        </abcs:commitmentPenalty>
                        <abcs:commitmentPenalty>
                            <!--Optional:-->
                            <abcs:commitmentName>asdasd asdasd</abcs:commitmentName>
                            <!--Optional:-->
                            <abcs:startDate>2013-04-10T16:06:05.664+02:00</abcs:startDate>
                            <!--Optional:-->
                            <abcs:endDate>2015-04-10T16:06:05.664+02:00</abcs:endDate>
                            <!--Optional:-->
                            <abcs:totalPeriod>720</abcs:totalPeriod>
                            <!--Optional:-->
                            <abcs:remainingPeriod>30</abcs:remainingPeriod>
                            <!--Optional:-->
                            <abcs:penaltyAmount>500</abcs:penaltyAmount>
                        </abcs:commitmentPenalty>
                    </abcs:listOfCommitmentPenalty>
                    <!--Optional:-->
                    <abcs:totalPenaltyAmount>1700</abcs:totalPenaltyAmount>
                </abcs:ResponseBody>
                <!--Optional:-->
                <ave:ResultStatus>
                    <ave:resultCode>0</ave:resultCode>
                    <!--Optional:-->
                    <ave:resultMessageList>
                        <!--Zero or more repetitions:-->
                        <ave:resultMessage>
                            <!--Optional:-->
                            <ave:resultMessageId>?</ave:resultMessageId>
                            <!--Optional:-->
                            <ave:resultMessageSubId>?</ave:resultMessageSubId>
                            <!--Optional:-->
                            <ave:resultStringCode>?</ave:resultStringCode>
                            <!--Optional:-->
                            <ave:resultDescription>?</ave:resultDescription>
                        </ave:resultMessage>
                    </ave:resultMessageList>
                    <!--Optional:-->
                    <ave:KeyValuePairList>
                        <!--Zero or more repetitions:-->
                        <ave:KeyValuePair>
                            <!--Optional:-->
                            <ave:key>?</ave:key>
                            <!--Optional:-->
                            <ave:value>?</ave:value>
                            <!--Optional:-->
                            <ave:data_type>?</ave:data_type>
                        </ave:KeyValuePair>
                    </ave:KeyValuePairList>
                </ave:ResultStatus>
            </ebs:EbsRetrieveProbableCommitmentPenaltiesResponse></soapenv:Body></soapenv:Envelope>

The Sql:

select atts.att1,atts.att2,my_table.PL_IN
    from my_table
         , xmltable(
                xmlnamespaces(
                    'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv"
                       , 'http://www.avea.com.tr/om/ABCS_BSCS_ASSET_OPERATIONS' as "abcs"
                      , 'http://www.avea.com.tr/AveaFrameWork' as "ave"
                      ,'http://www.avea.com.tr/om/EBS_COMMITMENT_ASSET_OPERATIONS' as "ebs")
                ,'/soapenv:Envelope/soapenv:Header/soapenv:Body/abcs:ResponseBody/abcs:listOfCommitmentPenalty/abcs:commitmentPenalty'
                passing xmltype(my_table.PL_IN)
                columns
                    att1 varchar2 (20) path 'abcs:commitmentName',
                    att2 number path 'abcs:totalPeriod') atts

I am trying to parse clob xml data in oracle no rows return. In the sql I define namespaces and parse xml, but it does not work. I've tried almost all the solutions I've done in Google, but it did not. Is there anyone that can help me or Anyone suggest for this question. Thanks.

Alex Poole · Accepted Answer · 2017-03-21 16:54:39Z

1

Your XPath is incorrect. soapenv:Body is at the same level as soapenv:Header, which is an empty tag. You've also missed out the ebs:EbsRetrieveProbableCommitmentPenaltiesResponse node at the top of the body.

So the XPath should start:

'/soapenv:Envelope/soapenv:Body/ebs:EbsRetrieveProbableCommitmentPenaltiesResponse/abcs:ResponseBody/...

With just that change your query gets:

ATT1                       ATT2 PL_IN                                                                           
-------------------- ---------- --------------------------------------------------------------------------------
asddsfdsf dsfsdf            365 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmln
asdasd asdasd               720 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmln

answered Mar 21, 2017 at 16:54

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to parse xml data from clob in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related