XMLTable without using namespaces in Oracle-CodePudding

I'm trying to extract xml values using XMLTable in PL/SQL.

DECLARE
xdata XMLType := XMLType(
    '<?xml version="1.0" encoding="UTF-8"?>
            <stusMsg xmlns="http://www.my.namespace.com/src">
            <messageHeader>
              <sourceSystem>PFS</sourceSystem>
              <originatingSystem>MRTI HKH</originatingSystem>
            </messageHeader>
            <messageDetail>
            <TradeRef>1033796</TradeRef>
            <TradeRefType>Ticket</TradeRefType>
            <Company>MY_CMPY</Company>
            <TradeGREF></TradeGREF>
            <TradeType>TRD</TradeType>
            <PriorityType>CRS</PriorityType>
            <Priority>1</Priority>
            </messageDetail>
            </stusMsg>');

CURSOR get_data(x XMLType) IS
SELECT * 
FROM XMLTABLE(xmlnamespaces(default 'http://www.my.namespace.com/src'),
   '/stusMsg/messageDetail'
   passing x
           COLUMNS 
           TradeRef      VARCHAR2(30)    PATH 'TradeRef',
           TradeRefType  VARCHAR2(30)    PATH 'TradeRefType');
BEGIN

FOR rec IN get_data(xdata) LOOP
  dbms_output.put_line(rec.TradeRef);
  dbms_output.put_line(rec.TradeRefType);
  END LOOP;
END;

If I don't use xmlnamespaces(default 'http://www.my.namespace.com/src') in XMLTABLE and namespace is present in the xml (xdata) , then the values are not being extracted.

Is there a way to tell XMLTABLE to ignore the namespace? so that program don't depend on xmlnamespaces

CodePudding user response：

Rather than trying to ignore the namespaces, if there is only a default namespace, you can remove the xmlns attribute from the document root:

DECLARE
  v_dom DBMS_XMLDOM.DOMDocument;
  xdata XMLTYPE := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <stusMsg xmlns="http://www.my.namespace.com/src">
    <messageHeader>
      <sourceSystem>PFS</sourceSystem>
      <originatingSystem>MRTI HKH</originatingSystem>
    </messageHeader>
    <messageDetail>
    <TradeRef>1033796</TradeRef>
    <TradeRefType>Ticket</TradeRefType>
    <Company>MY_CMPY</Company>
    <TradeGREF></TradeGREF>
    <TradeType>TRD</TradeType>
    <PriorityType>CRS</PriorityType>
    <Priority>1</Priority>
    </messageDetail>
    </stusMsg>');
  CURSOR get_data(x XMLType) IS
    SELECT * 
    FROM XMLTABLE(
           '/stusMsg/messageDetail'
           passing x
           COLUMNS 
             TradeRef      VARCHAR2(30)    PATH 'TradeRef',
             TradeRefType  VARCHAR2(30)    PATH 'TradeRefType'
         );
BEGIN
  v_dom  := DBMS_XMLDOM.NEWDOMDOCUMENT(xdata);
  DBMS_XMLDOM.REMOVEATTRIBUTE(
    DBMS_XMLDOM.GETDOCUMENTELEMENT(v_dom),
    'xmlns'
  );
  xdata := DBMS_XMLDOM.GETXMLTYPE(v_dom);
  
  FOR rec IN get_data(xdata) LOOP
    dbms_output.put_line(rec.TradeRef);
    dbms_output.put_line(rec.TradeRefType);
  END LOOP;
END;
/

Which outputs:

1033796
Ticket

db<>fiddle here

CodePudding user response：

If you really don't want to honour the namespaces in the XML document you can wildcard all of the node references:

SELECT * 
FROM XMLTABLE(
   '/*:stusMsg/*:messageDetail'
   passing x
           COLUMNS 
           TradeRef      VARCHAR2(30)    PATH '*:TradeRef',
           TradeRefType  VARCHAR2(30)    PATH '*:TradeRefType');

db<>fiddle

But that seems less than ideal; I'd use them properly if you can.