Skip to Main Content
  • Questions

  • Extract element from XML Data using SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bhavesh.

Asked: May 19, 2017 - 9:24 pm UTC

Last updated: May 23, 2017 - 1:56 am UTC

Version: 12c Rel1

Viewed 1000+ times

You Asked 

Dear TOM,

I like to extract simple data element from XML data using SQL. Could you please help, 

Option1:

SELECT * 
FROM XMLTable( '/entry/'
     passing xmltype(
 '<entry xmlns="http://www.w3.org/2005/Atom">
 <id>https://toitest1.servicebus.net/oracle1</id>
 <title type="text">oracle1</title>
 <published>2016-11-30T19:32:43Z</published>
 <updated>2017-05-02T14:43:51Z</updated>
 <author><name>toitest1</name></author>
 <link rel="self" href="https://toitest1.servicebus.net/oracle1"/>
 <content type="application/xml">
 <QueueDescription xmlns="http://schemas.servicebus.com/netservices/2010/10/servicebus/connect" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
     <LockDuration>PT30S</LockDuration>
     <MaxSizeInMegabytes>16384</MaxSizeInMegabytes>
     <RequiresDuplicateDetection>false</RequiresDuplicateDetection>
     <RequiresSession>false</RequiresSession>
     <DefaultMessageTimeToLive>P14D</DefaultMessageTimeToLive>
     <DeadLetteringOnMessageExpiration>false</DeadLetteringOnMessageExpiration>
     <DuplicateDetectionHistoryTimeWindow>PT10M</DuplicateDetectionHistoryTimeWindow>
     <MaxDeliveryCount>10</MaxDeliveryCount><EnableBatchedOperations>true</EnableBatchedOperations>
     <SizeInBytes>357</SizeInBytes>
     <MessageCount>1</MessageCount>
 </QueueDescription>
 </content>
 </entry>') 
 columns id varchar2(50) path 'id',
      title varchar2(50) path 'title'
        cnt varchar2(10) path 'QueueDescription/MessageCount'                 
   ) xt

Option2:

SELECT xt.column_value.extract('/entry/id').getstringval()
FROM XMLTable(
      '<entry xmlns="http://www.w3.org/2005/Atom">
 <id>https://toitest1.servicebus.net/oracle1</id>
 <title type="text">oracle1</title>
 <published>2016-11-30T19:32:43Z</published>
 <updated>2017-05-02T14:43:51Z</updated>
 <author><name>toitest1</name></author>
 <link rel="self" href="https://toitest1.servicebus.net/oracle1"/>
 <content type="application/xml">
 <QueueDescription xmlns="http://schemas.servicebus.com/netservices/2010/10/servicebus/connect" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
     <LockDuration>PT30S</LockDuration>
     <MaxSizeInMegabytes>16384</MaxSizeInMegabytes>
     <RequiresDuplicateDetection>false</RequiresDuplicateDetection>
     <RequiresSession>false</RequiresSession>
     <DefaultMessageTimeToLive>P14D</DefaultMessageTimeToLive>
     <DeadLetteringOnMessageExpiration>false</DeadLetteringOnMessageExpiration>
     <DuplicateDetectionHistoryTimeWindow>PT10M</DuplicateDetectionHistoryTimeWindow>
     <MaxDeliveryCount>10</MaxDeliveryCount><EnableBatchedOperations>true</EnableBatchedOperations>
     <SizeInBytes>357</SizeInBytes><MessageCount>1</MessageCount>
 </QueueDescription>
 </content>
 </entry>'                 
   ) xt    

Many Thanks,
Bhavesh

and Connor said...

Either is fine, but if you just need a single element, then option 2 seems the more natural one because the SQL is more "self documenting", ie, the person maintaining your code knows you were after that explicit value.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

XMLQuery

Bhavesh Shah, May 20, 2017 - 2:10 pm UTC

Dear Connor,
Thanks for checking and providing your feedback on approach, however, either of the option doesn't work, query doesn't return any value and that's where I needed help. I tried multiple paths but no luck. Could you please take another look?
Thanks,
Bhavesh
Connor McDonald
May 23, 2017 - 1:56 am UTC

"however, either of the option doesn't work, query doesn't return any value and that's where I needed help"

Perhaps you could provide that piece of information in the original question. We're not psychic :-)

You need to let XMLTABLE know about the namespaces

SQL> SELECT *
  2  FROM XMLTable( XMLNAMESPACES (
  3                  default 'http://www.w3.org/2005/Atom')
  4                  ,
  5        '/entry'
  6       passing xmltype(
  7   '<entry xmlns="http://www.w3.org/2005/Atom">
  8   <id>https://toitest1.servicebus.net/oracle1</id>
  9   <title type="text">oracle1</title>
 10   <published>2016-11-30T19:32:43Z</published>
 11   <updated>2017-05-02T14:43:51Z</updated>
 12   <author><name>toitest1</name></author>
 13   <link rel="self" href="https://toitest1.servicebus.net/oracle1"/>
 14   <content type="application/xml">
 15   <QueueDescription xmlns="http://schemas.servicebus.com/netservices/2010/10/servicebus/connect" xmlns:i="http://www.w3.org/2001/XMLSchema-instance
 16       <LockDuration>PT30S</LockDuration>
 17       <MaxSizeInMegabytes>16384</MaxSizeInMegabytes>
 18       <RequiresDuplicateDetection>false</RequiresDuplicateDetection>
 19       <RequiresSession>false</RequiresSession>
 20       <DefaultMessageTimeToLive>P14D</DefaultMessageTimeToLive>
 21       <DeadLetteringOnMessageExpiration>false</DeadLetteringOnMessageExpiration>
 22       <DuplicateDetectionHistoryTimeWindow>PT10M</DuplicateDetectionHistoryTimeWindow>
 23       <MaxDeliveryCount>10</MaxDeliveryCount><EnableBatchedOperations>true</EnableBatchedOperations>
 24       <SizeInBytes>357</SizeInBytes>
 25       <MessageCount>1</MessageCount>
 26   </QueueDescription>
 27   </content>
 28   </entry>')
 29   columns id varchar2(50) path 'id',
 30        title varchar2(50) path 'title',
 31          cnt varchar2(10) path 'QueueDescription/MessageCount'
 32     ) xt;

ID
--------------------------------------------------
TITLE                                              CNT
-------------------------------------------------- ----------
https://toitest1.servicebus.net/oracle1
oracle1



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.