Extraer un elemento de XML con EXTRACT desde una tabla en Oracle con Namespace.

Ejemplo con una tabla con campo XML

CREATE TABLE mytablechar ( id, xmlcolumn ) AS
SELECT
  1,
  '<report xmlns:nsl="http://xmlns.oracle.com/ns/order">
<nsl:InquireUnifiedCustomerProfileByServiceLocationRequest>
<AccountIdentifier>
<AddressIdentifier>
<AddressId>MyAddress 1020, North Hampton, Va</AddressId>
<FullCriteria>1</FullCriteria>
</AddressIdentifier>
</AccountIdentifier>
</nsl:InquireUnifiedCustomerProfileByServiceLocationRequest>
</report>'
  FROM DUAL;
WITH
  xmlData
AS
  (SELECT
     XMLTYPE(t.xmlcolumn) xml
   FROM
     mytablechar t
     where t.id = 1 )
  SELECT
    x.xml.EXTRACT(
    '/report/ns1:InquireUnifiedCustomerProfileByServiceLocationRequest/AccountIdentifier/AddressIdentifier/addressId',
    'xmlns:ns1="http://xmlns.oracle.com/ns/order"').getStringVal()
  FROM
    xmlData x;

CREATE TABLE mytableclob (id number, xmlcolumn clob);

INSERT INTO mytableclob 
SELECT
  1,
  '<report xmlns:nsl="http://xmlns.oracle.com/ns/order">
<nsl:InquireUnifiedCustomerProfileByServiceLocationRequest>
<AccountIdentifier>
<AddressIdentifier>
<AddressId>MyAddress 1020, North Hampton, Va</AddressId>
<FullCriteria>1</FullCriteria>
</AddressIdentifier>
</AccountIdentifier>
</nsl:InquireUnifiedCustomerProfileByServiceLocationRequest>
</report>'
FROM DUAL;

COMMIT;

WITH
  xmlData
AS
  (SELECT
     XMLTYPE(t.xmlcolumn) xml
   FROM
     mytableclob t
   WHERE
     t.id = 1)
  SELECT
    x.xml.EXTRACT(
    '/report/ns1:InquireUnifiedCustomerProfileByServiceLocationRequest/AccountIdentifier/AddressIdentifier/AddressId',
    'xmlns:ns1="http://xmlns.oracle.com/ns/order"').getStringVal()
  FROM
xmlData x;

WITH
  xmlData
AS
  (SELECT
     XMLTYPE(t.xmlcolumn) xml
   FROM
     mytablechar t
   WHERE
     t.id = 1)
  SELECT
    x.xml.EXTRACT(
    '/report/ns1:InquireUnifiedCustomerProfileByServiceLocationRequest/AccountIdentifier/AddressIdentifier/AddressId',
    'xmlns:ns1="http://xmlns.oracle.com/ns/order"').getStringVal()
  FROM
xmlData x;

drop table mytablechar;

drop table mytableclob;

La salida de este comando al ejecutarlo como script es

Table MYTABLECHAR creado.


X.XML.EXTRACT('/REPORT/NS1:INQUIREUNIFIEDCUSTOMERPROFILEBYSERVICELOCATIONREQUEST/ACCOUNTIDENTIFIER/ADDRESSIDENTIFIER/ADDRESSID','XMLNS:NS1="HTTP://XMLNS.ORACLE.COM/NS/ORDER"').GETSTRINGVAL()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Table MYTABLECLOB creado.


1 fila insertadas.

Confirmación terminada.

X.XML.EXTRACT('/REPORT/NS1:INQUIREUNIFIEDCUSTOMERPROFILEBYSERVICELOCATIONREQUEST/ACCOUNTIDENTIFIER/ADDRESSIDENTIFIER/ADDRESSID','XMLNS:NS1="HTTP://XMLNS.ORACLE.COM/NS/ORDER"').GETSTRINGVAL()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<AddressId>MyAddress 1020, North Hampton, Va</AddressId>


X.XML.EXTRACT('/REPORT/NS1:INQUIREUNIFIEDCUSTOMERPROFILEBYSERVICELOCATIONREQUEST/ACCOUNTIDENTIFIER/ADDRESSIDENTIFIER/ADDRESSID','XMLNS:NS1="HTTP://XMLNS.ORACLE.COM/NS/ORDER"').GETSTRINGVAL()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<AddressId>MyAddress 1020, North Hampton, Va</AddressId>


Table MYTABLECHAR borrado.


Table MYTABLECLOB borrado.