CODEWITHSUNDEEP
sqlxmloracle-databaseplsql
Andrew Burns
Andrew Burns

Reputation: 346

Selecting a Attribute value from Oracle Clob XML

I have the following clob xml in my database:

<HXML
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<BATCH>
    <FOLDER Class="SDER" AppUserLocation="0001" AppUserID="">
        <DOCUMENTS>
            <DOCUMENT ScanDocID="28/11/2013 0805353505 LNFDDDF" Type="RLAFASDFSSESS" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
             <DOCUMENT ScanDocID="28/11/2013 343 LNFDDDF" Type="FDSF" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
             <DOCUMENT ScanDocID="28/11/2013 3434 LNFDDDF" Type="FASDFASD" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
        </DOCUMENTS>
    </FOLDER>
</BATCH>

And I am trying to return the ScanDocID attribute from the DOCUMENT tag.

I have tried the following:

with 
   xmlData 
    as 
( select
      XMLTYPE(x.xmldoc) xml 
 from
      c_xml_doc x 
  where x.cxd_id in ('269814','269815','247336','269816')) 
      select 
          x.xml.EXTRACT('//HXML/BATCH/FOLDER/DOCUMENTS/DOCUMENT["ScanDocID"]/text()')
  from 
      xmlData x ;

but i am not getting any rows returned. I have tried a similar query on an element and that returns the correct results

Upvotes: 0

Views: 757

Answers (1)

Boneist
Boneist

Reputation: 23578

EXTRACT and EXTRACTVALUE have been deprecated. Instead, you should use XMLTABLE, something like:

with 
   xmlData 
    as 
( select 1 id,
      XMLTYPE('<HXML
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<BATCH>
    <FOLDER Class="SDER" AppUserLocation="0001" AppUserID="">
        <DOCUMENTS>
            <DOCUMENT ScanDocID="28/11/2013 0805353505 LNFDDDF" Type="RLAFASDFSSESS" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
             <DOCUMENT ScanDocID="28/11/2013 343 LNFDDDF" Type="FDSF" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
             <DOCUMENT ScanDocID="28/11/2013 3434 LNFDDDF" Type="FASDFASD" Processed="Y">
                <IMAGE FileName="\\sitFDAF16\FDS\radCB056.pdf" CRC="FSDAF"></IMAGE>
            </DOCUMENT>
        </DOCUMENTS>
    </FOLDER>
</BATCH>
</HXML>') xmld
 from
      dual x) 
select x.id,
       xt.scan_doc_id,
       xt.image_file_name,
       xt.image_crc
from   xmlData x,
       xmltable('/HXML/BATCH/FOLDER/DOCUMENTS/DOCUMENT'
                passing x.xmld
                columns scan_doc_id varchar2(50) path '@ScanDocID',
                        image_file_name varchar2(100) path 'IMAGE/@FileName',
                        image_crc varchar2(20) path 'IMAGE/@CRC') xt;

Upvotes: 1

Related Questions