Oracle 12c: Extract data (select) from XML (CLOB Type)

Question

I need to extract some tag values from a XML code saved in a CLOB type column in Oracle 12c table.

Earlier we were using xmltype(COLUMN).extract('XPath/text()').getStringVal() to extract data from tags but its not working after our database upgrade to 12c.

We have XML Like:

<otm:ShipmentStatus 
xmlns:gtm="http://xmlns.oracle.com/apps/gtm/transmission/v6.4" 
xmlns:otm="http://xmlns.oracle.com/apps/otm/transmission/v6.4">
<otm:ServiceProviderAlias>
<otm:ServiceProviderAliasQualifierGid>
<otm:Gid>
<otm:Xid>GLOG</otm:Xid>
</otm:Gid>
</otm:ServiceProviderAliasQualifierGid>
<otm:ServiceProviderAliasValue>TEST.123</otm:ServiceProviderAliasValue>
</otm:ServiceProviderAlias>
<otm:IntSavedQuery>
<otm:IntSavedQueryGid>
<otm:Gid>
<otm:DomainName>TEST</otm:DomainName>
<otm:Xid>FIND_DELIVERY_NUMBER</otm:Xid>
</otm:Gid>......etc.

From this XML we have to select some values.

Please suggest some way to solve this problem. Feel free to ask if you need anything more.

Thank You. Satyam

docs.oracle.com/database/121/SQLRF/functions068.htm#SQLRF00640 — Rene
You have to extract "some values"? If your business customers came to you with a request formulated exactly in those words, what would you do? — mathguy

Arkadiusz Łukasiewicz Arkadiusz Łukasiewicz · Accepted Answer · 2017-09-14T09:53:00

Example xml has namespaces. And you have to use it.

xmltype.extract('/otm:ShipmentStatus', 'xmlns:gtm="http://xmlns.oracle.com/apps/gtm/transmission/v6.4" 
xmlns:otm="http://xmlns.oracle.com/apps/otm/transmission/v6.4"')  extacting node from specify namespace 

xmltype.extract('/*:ShipmentStatus')  extractin node from any namespace

Oracle 12c: Extract data (select) from XML (CLOB Type)

1 Answers