xml which is present ina clob column of xml_message table : 
<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:oag="http://gateway.consumerhub.commercial.volvocars.net/oagis" xmlns:oag1="http://www.openapplications.org/oagis" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
        <oag:UpdateConsumerAndOpportunityData_v1 xmlns:ns3="http://www.ford.com/oagis">
            <oag1:ApplicationArea>
                <oag1:Sender>
                    <oag1:LogicalId>Volvo</oag1:LogicalId>
                    <oag1:Component>Vista</oag1:Component>
                    <oag1:Task>SyncConsumer</oag1:Task>
                    <oag1:AuthorizationId>AUTH-SK034</oag1:AuthorizationId>
                </oag1:Sender>
                <oag1:CreationDateTime>2016-01-27T15:07:47Z</oag1:CreationDateTime>
                <oag1:BODId>sk-connect-034</oag1:BODId>
            </oag1:ApplicationArea>
            <oag:DataArea>
                <oag1:Sync confirm="Always">
                    <oag1:SyncCriteria expressionLanguage="XPath">
                        <oag1:SyncExpression action="change"/>
                    </oag1:SyncCriteria>
                </oag1:Sync>
                <oag:Consumer>
                    <oag:Header>
                        <oag:Source>AU</oag:Source>
                    </oag:Header>
                    <oag:ConsumerDetails Category="Person">
                        <oag:ConsumerId/>
                        <oag:Language>EN</oag:Language>
                        <oag:PersonDetails>
                            <oag:FirstName>firstname_email_034</oag:FirstName>
                            <oag:Surname>sname_email_034</oag:Surname>
                        </oag:PersonDetails>
                        <oag:EmailAddress Active="true">[email protected]</oag:EmailAddress>
                        <oag:EmailAddress Active="true" Usage="Business">[email protected]</oag:EmailAddress>
                        <oag:EmailAddress Active="true" Usage="Invoice">[email protected]</oag:EmailAddress>
                    </oag:ConsumerDetails>
                </oag:Consumer>
            </oag:DataArea>
        </oag:UpdateConsumerAndOpportunityData_v1>
    </soapenv:Body>
</soapenv:Envelope>

Now i want all the email address from this xml to fetch and insert into another table. this xml contain 3 email address ,i want till n number of email address to be fetched that a xml file will contain

CodePudding user response：

As the linked answer shows, you can use XMLTable to extract multiple values; but you need to define at least the namespaces you use, and construct the XPath properly. So to get just the email addresses you can do:

select x.emailaddress
from xml_message m
cross apply xmltable (
  xmlnamespaces (
      'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
      'http://gateway.consumerhub.commercial.volvocars.net/oagis' as "oag",
      'http://www.openapplications.org/oagis' as "oag1",
      'http://www.ford.com/oagis' as "ns3"
  ),
  'soapenv:Envelope/soapenv:Body/oag:UpdateConsumerAndOpportunityData_v1/oag:DataArea/oag:Consumer/oag:ConsumerDetails/oag:EmailAddress'
  passing xmltype(m.xml_clob)
  columns
    EmailAddress varchar2(64) path '.'
) x;

The XPath doesn't refer to oag1 or ns3 but it doesn't hurt to include them anyway, and you might need them to extract other information from the same XML document.

This also lets you extract multiple values at once; or to filter base on an attribute - you might, for example, only want to include the email addresses with attribute Active set to true, which you can do by adding that condition to the XPath:

  'soapenv:Envelope/soapenv:Body/oag:UpdateConsumerAndOpportunityData_v1/oag:DataArea/oag:Consumer/oag:ConsumerDetails/oag:EmailAddress[@Active="true"]'

db<>fiddle

You can read more about manipulating XML in the documentation.