exporting xml value from CLOB data field in oracle using multiple namespace

Question

I have a table which contains x number of records. One of the fields is a CLOB and contains XML with a particular field Here is a very shortened version of the XML

 <metadata xml:lang="en"
 xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:srv="http://www.isotc211.org/2005/srv" xmlns:gts="http://www.isotc211.org/2005/gts" xmlns:gml="http://www.opengis.net/gml" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:msxsl="urn:schemas-microsoft-com:xslt">
  <gmd:GEMINI_Metadata>
        <gmd:fileIdentifier>
            <gco:CharacterString>cf40a39a-0721-4fd4-84f3-adc28aee1158</gco:CharacterString>
        </gmd:fileIdentifier>
    <gmd:dateStamp>
        <gco:Date>2019-01-16</gco:Date>
    </gmd:dateStamp>
  </gmd:GEMINI_Metadata>
</metadata>

What I would like to do is get characterstring value from the fileIdentifier tag using SQL I have tried the following

select EXTRACT (XMLType (DOCUMENTATION), '//fileIdentifier//gco:CharacterString','xmlns:gmd="http://www.isotc211.org/2005/gmd"', 'xmlns:gco="http://www.isotc211.org/2005/gco"') as DOCUMENTATION from sde.gdb_items_vw where name = 'testTable'

i get the following

ORA-00939: too many arguments for function

If I try only specifying one tag and one namespace like this

select EXTRACT (XMLType (DOCUMENTATION), '//gmd:fileIdentifier','xmlns:gmd="http://www.isotc211.org/2005/gmd"') as DOCUMENTATION from sde.gdb_items_vw where name = 'testTable';

I get the following

DOCUMENTATION
--------------------------------------------------------------------------------
<gmd:fileIdentifier xmlns:gmd="http://www.isotc211.org/2005/gmd"><gco:CharacterS

So what is the correct way of getting a particular tag that has multiple namespaces within its tree?

Thomas Kirchhoff · Accepted Answer · 2021-02-01 16:19:07Z

1

The best method is to use XMLTABLE. Here you can easily specify the namespaces.

SELECT doc AS documentation
  FROM sde.gdb_items_vw,
       XMLTABLE( xmlnamespaces( 'http://www.isotc211.org/2005/gmd' AS "gmd",
                                'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                                'http://www.isotc211.org/2005/gco' AS "gco",
                                'http://www.isotc211.org/2005/srv' AS "srv",
                                'http://www.isotc211.org/2005/gts' AS "gts",
                                'http://www.opengis.net/gml' AS "gml",
                                'http://www.w3.org/1999/xlink' AS "xlink",
                                'urn:schemas-microsoft-com:xslt' AS "msxsl"                                
       ), 
       '/metadata' PASSING XMLTYPE(documentation)
       COLUMNS doc VARCHAR2(1000) PATH 'gmd:fileIdentifier/gco:CharacterString'
       );

Result:

DOCUMENTATION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
------------------------------------
cf40a39a-0721-4fd4-84f3-adc28aee1158

edited Feb 1, 2021 at 16:19

answered Jan 29, 2021 at 19:09

Thomas Kirchhoff

1,1181 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MapMan Over a year ago

Thanks, but that doesnt seem to be picking up the value "cf40a39a-0721-4fd4-84f3-adc28aee1158" from the xml, if i select name (as in your answer), the name appears, but removing that, nothing appears from xml - which i need the text between the characterString tag

Thomas Kirchhoff Over a year ago

@MapMan OK, I only had the "name" column in my test. I have removed it from the query. But the result of the query should be what you want: cf40a39a-0721-4fd4-84f3-adc28aee1158

MapMan Over a year ago

i missed out a tag on the example xml (</gmd:GEMINI_Metadata>) which i have added, however before the PASSING keyword, i have replaced '\metadata' with '"\ gmd:GEMINI_Metadata" but nothing appears

Thomas Kirchhoff Over a year ago

@MapMan You just need to adjust the path. PATH 'gmd:GEMINI_Metadata/gmd:fileIdentifier/gco:CharacterString'

Collectives™ on Stack Overflow

exporting xml value from CLOB data field in oracle using multiple namespace

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related