Complex XML processing in Oracle

Question

Can anyone please tell how I can process complex XML in oracle? For e.g I have the XML below:

<?xml version="1.0" encoding="utf-8" ?> 
<UserEmailPreferences>
 <EmailOffering>
  <EmailGroupID>1</EmailGroupID> 
  <EmailOfferingID>0</EmailOfferingID> 
  <Service>
    <val>service-1</val>
    <val>service-2</val>
  </Service> 
  <Title>Clinical Focus</Title> 
  <SubscriptionOption>D</SubscriptionOption> 
  </EmailOffering>
</UserEmailPreferences>
<UserEmailPreferences>
 <EmailOffering>
  <EmailGroupID>1</EmailGroupID> 
  <EmailOfferingID>0</EmailOfferingID> 
  <Service>
    <val>service-1</val>
  </Service> 
  <Title>XYZ</Title> 
  <SubscriptionOption>D</SubscriptionOption> 
  </EmailOffering>
</UserEmailPreferences>

In the above XML, I have two <UserEmailPreferences> nodes. It can be two, three or whatever...That is dynamic.

In <UserEmailPreferences> node, I have <Service> block which is again dynamic for corresponding <EmailOffering> node.

Can anyone please help me how I can process such XML in Oracle?

Community · Accepted Answer · 2017-05-23 12:20:37Z

you can use the method described in the SO question "Oracle Pl/SQL: Loop through XMLTYPE nodes", for example:

SQL> SELECT extractvalue(column_value,'/EmailOffering/EmailGroupID') "GrpID",
  2         extractvalue(column_value,'/EmailOffering/EmailOfferingID')"OfrID",
  3         extractvalue(column_value,'/EmailOffering/Title') "Title",
  4         extractvalue(column_value,'/EmailOffering/SubscriptionOption')"Op",
  5         extract(COLUMN_VALUE, '/EmailOffering/Service') service
  6    FROM TABLE(XMLSequence(XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
  7  <document>
  8     <UserEmailPreferences>
  9      <EmailOffering>
 10       <EmailGroupID>1</EmailGroupID>
 11       <EmailOfferingID>0</EmailOfferingID>
 12       <Service>
 13         <val>service-1</val>
 14         <val>service-2</val>
 15       </Service>
 16       <Title>Clinical Focus</Title>
 17       <SubscriptionOption>D</SubscriptionOption>
 18       </EmailOffering>
 19     </UserEmailPreferences>
 20     <UserEmailPreferences>
 21      <EmailOffering>
 22       <EmailGroupID>1</EmailGroupID>
 23       <EmailOfferingID>0</EmailOfferingID>
 24       <Service>
 25         <val>service-1</val>
 26       </Service>
 27       <Title>XYZ</Title>
 28       <SubscriptionOption>D</SubscriptionOption>
 29       </EmailOffering>
 30     </UserEmailPreferences>
 31  </document>').extract('/document/UserEmailPreferences/EmailOffering'))) t;

GrpID  OfrID  Title                Op     SERVICE
------ ------ -------------------- ------ -------------------------------------
1      0      Clinical Focus       D      <Service>
                                            <val>service-1</val>
                                            <val>service-2</val>
                                          </Service>

1      0      XYZ                  D      <Service>
                                            <val>service-1</val>
                                          </Service>

Note that your XML wasn't a valid document since it was missing an enclosing tag (I added the tag /document)

Collectives™ on Stack Overflow

Complex XML processing in Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related