How do I query xml clob data in Oracle 11

Question

I need to extract submissionId and ReturnSubmStatus from the following clob on my table lead_reporting_clob.

with lead_reporting_clob as (
  select xmltype('
<?xml version="1.0" encoding="utf-8"?>
<LeadReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.fin.flu/efile">
  <Lead>
    <SubmissionId>12345678emvuq</SubmissionId>
    <PatternDescription>OLF - R</PatternDescription>
    <SourceIPAddress>
      <IPAddress>
        <IPv4AddressTxt>172.56.18.224</IPv4AddressTxt>
      </IPAddress>
      <TCPPortNumber>443</TCPPortNumber>
      <IPTs>2016-06-30T18:54:20-04:00</IPTs>
    </SourceIPAddress>
    <SourceDeviceId>
      <DeviceId>9D44D3BD1A014079D888</DeviceId>
      <DeviceIdTs>2016-06-30T18:54:20-04:00</DeviceIdTs>
    </SourceDeviceId>
    <AdditionalInformation>
      <ReturnSubmStatus>A</ReturnSubmStatus>
      <EFIN>530</EFIN>
      <EmailAddressDomain>OUTLOOK.COM</EmailAddressDomain>
      <PhoneNumberLastFour>5103</PhoneNumberLastFour>
    </AdditionalInformation>
  </Lead>
  <Lead>
    <SubmissionId>5303942016182rkv20ij</SubmissionId>
    <PatternDescription>OLF - R</PatternDescription>
    <SourceIPAddress>
      <IPAddress>
        <IPv4AddressTxt>172.56.18.224</IPv4AddressTxt>
      </IPAddress>
      <TCPPortNumber>443</TCPPortNumber>
      <IPTs>2016-06-30T18:39:18-04:00</IPTs>
    </SourceIPAddress>
    <SourceDeviceId>
      <DeviceId>9D44D3BD1A014079D666</DeviceId>
      <DeviceIdTs>2016-06-30T18:39:18-04:00</DeviceIdTs>
    </SourceDeviceId>
    <AdditionalInformation>
      <ReturnSubmStatus>A</ReturnSubmStatus>
      <EFIN>530</EFIN>
      <EmailAddressDomain>OUTLOOK.COM</EmailAddressDomain>
      <PhoneNumberLastFour>4855</PhoneNumberLastFour>
    </AdditionalInformation>
  </Lead>
</LeadReport>'
) as shared_xml
  from dual)

I am not sure whether I should be using xmltable or extract. I have tried the following but I am getting an error, xml parsing failed. I have read many posts on the subject but I think I really have no idea what I am doing.

Thank you for any help.

SELECT 
   XMLTYPE(shared_xml).EXTRACT('//SubmissionId/text()'),
   XMLTYPE(shared_xml).EXTRACT('//ReturnSubmStatus/text()') 
FROM 
   lead_reporting_clob

please check the link if this is can help you. http://stackoverflow.com/questions/13600480/extract-data-from-xml-clob-using-sql-from-oracle-database?rq=1 — XING, Jul 16 '16 at 10:11
Thanks, Raj_Te. I am getting ora-00932 inconsistent data types with the following: SELECT EXTRACTVALUE(shared_xml, '/Lead/SubmissionId') FROM lead_reporting_clob; — Marianne, Jul 16 '16 at 11:10
I am still stuck on this. I have been reading on it all day but can't seem to get the correct syntax. Either I get a parsing error or invalid type or number of arguments when trying to use create.xmltype. Can anyone offer some assistance? — Marianne, Jul 16 '16 at 21:20
Try these 3. I hope either of them should work. SELECT xmltype(z.A).extract('//SubmissionId/text()').getStringVal() FROM lead_reporting_clob z; select extract(xmltype(A),'//Lead/SubmissionId/text()').getStringVal() from lead_reporting_clob ; select extractvalue(xmltype(A), '//Lead/PatternDescription') from lead_reporting_clob; — XING, Jul 17 '16 at 09:40
For me it worked..Am not sure which oracle version you are working with. — XING, Jul 18 '16 at 15:19

How do I query xml clob data in Oracle 11

0 Answers0