add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table xml_message (xml_clob) as
select '<?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">ticc888@yahoo.com</oag:EmailAddress>
<oag:EmailAddress Active="true" Usage="Business">ahng2@hotmail.com</oag:EmailAddress>
<oag:EmailAddress Active="true" Usage="Invoice">jsmith@gmail.com</oag:EmailAddress>
</oag:ConsumerDetails>
1 rows affected
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;
EMAILADDRESS
ticc888@yahoo.com
ahng2@hotmail.com
jsmith@gmail.com
select x.emailaddress, x.active
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 '.',
Active varchar2(5) path '@Active'
) x;
EMAILADDRESS ACTIVE
ticc888@yahoo.com true
ahng2@hotmail.com true
jsmith@gmail.com true
drop table xml_message;
create table xml_message (xml_clob) as
select '<?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">ticc888@yahoo.com</oag:EmailAddress>
<oag:EmailAddress Active="true" Usage="Business">ahng2@hotmail.com</oag:EmailAddress>
<oag:EmailAddress Active="false" Usage="Invoice">jsmith@gmail.com</oag:EmailAddress>
</oag:ConsumerDetails>
1 rows affected
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[@Active="true"]'
passing xmltype(m.xml_clob)
columns
EmailAddress varchar2(64) path '.'
) x;
EMAILADDRESS
ticc888@yahoo.com
ahng2@hotmail.com