I'm trying to parse an XML in PL/SQL. I am unable to retrieve the attribute values from a tag, what am i doing wrong?

Question

The following XML won't parse. Please find the exact code I'm using -

DECLARE
  x XMLType := XMLType(
    '<ns0:CreateSODSOrder xmlns:ns0="http://schemas.dell.com/services/isp/OrderHistory/3.5" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" MessageType="CREATE">
  <ns0:CreateOrder>
    <ns0:SalesOrder ORDER_NUM="302199286" DPID="GB2003-2120-85049" PO_NUM="test" ORDER_BUID="202" DPS_NUM="" CHANNEL="ENTP" SUB_CHANNEL="GB_IC1A" ORDER_TYPE="GB ENT Order" REBOOKED_ORDER_NUM="" SALES_REP_NAME="SANJANA_D_C" ORIGINAL_ORDER_REF_NUM="" AMF_ORDER_NUM="" IR_NUM="GB2003-2120-85049" CCN="GB_IC1A" CUSTOMER_NUM="GB90117414" CUSTOMER_BUID="202" ORDER_DATE="2019-09-30T07:59:32" COUNTRY_NUM="GB" CCN_DESC="GB Indirect Certified  Tier 1A"/>
  </ns0:CreateOrder>
</ns0:CreateSODSOrder>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(column_value,'Sales/@ORDER_NAME') as name
         -- ,ExtractValue(Value(p),'/row/Address/State/text()') as state
          --,ExtractValue(Value(p),'/row/Address/City/text()') as city
    FROM   TABLE(XMLSequence(Extract(x,'/CreateSODSOrder/CreateOrder/SalesOrder')))
    ) LOOP
          dbms_output.put_line(r.name);
  END LOOP;
    dbms_output.put_line('out');
END;

When I use ns0: with the CreateSODSOrder/CreateOrder/SalesOrder, it throws an XML parsing error.

Answer 1

You are getting the XML parse error because namespace definition is missing. Following code will solve the namespace problem but it's hard to get values without knowing what you intend

DECLARE
  x XMLType := XMLType(
    '<ns0:CreateSODSOrder xmlns:ns0="http://schemas.dell.com/services/isp/OrderHistory/3.5" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" MessageType="CREATE">
  <ns0:CreateOrder>
    <ns0:SalesOrder ORDER_NUM="302199286" DPID="GB2003-2120-85049" PO_NUM="test" ORDER_BUID="202" DPS_NUM="" CHANNEL="ENTP" SUB_CHANNEL="GB_IC1A" ORDER_TYPE="GB ENT Order" REBOOKED_ORDER_NUM="" SALES_REP_NAME="SANJANA_D_C" ORIGINAL_ORDER_REF_NUM="" AMF_ORDER_NUM="" IR_NUM="GB2003-2120-85049" CCN="GB_IC1A" CUSTOMER_NUM="GB90117414" CUSTOMER_BUID="202" ORDER_DATE="2019-09-30T07:59:32" COUNTRY_NUM="GB" CCN_DESC="GB Indirect Certified  Tier 1A"/>
  </ns0:CreateOrder>
</ns0:CreateSODSOrder>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(column_value,'Sales/@ORDER_NAME') as name
         -- ,ExtractValue(Value(p),'/row/Address/State/text()') as state
          --,ExtractValue(Value(p),'/row/Address/City/text()') as city
    FROM   TABLE(XMLSequence(Extract(x,'/ns0:CreateSODSOrder/ns0:CreateOrder/ns0:SalesOrder', 'xmlns:ns0="http://schemas.dell.com/services/isp/OrderHistory/3.5"' )))
    ) LOOP
          dbms_output.put_line(r.name);
  END LOOP;
    dbms_output.put_line('out');
END;

It's a good idea to use XMLTABLE since ExtractValue is bit old fashioned and deprecated.

Example code to get attributes with XMLTABLE is added below.

DECLARE
CURSOR get_data IS
SELECT * 
FROM XMLTABLE(xmlnamespaces('http://schemas.dell.com/services/isp/OrderHistory/3.5' as "ns0", 'http://schemas.xmlsoap.org/soap/envelope/' as "soap-env" ),'ns0:CreateSODSOrder/ns0:CreateOrder/ns0:SalesOrder'
   passing xmltype('<ns0:CreateSODSOrder xmlns:ns0="http://schemas.dell.com/services/isp/OrderHistory/3.5" xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" MessageType="CREATE">
  <ns0:CreateOrder>
    <ns0:SalesOrder ORDER_NUM="302199286" DPID="GB2003-2120-85049" PO_NUM="test" ORDER_BUID="202" DPS_NUM="" CHANNEL="ENTP" SUB_CHANNEL="GB_IC1A" ORDER_TYPE="GB ENT Order" REBOOKED_ORDER_NUM="" SALES_REP_NAME="SANJANA_D_C" ORIGINAL_ORDER_REF_NUM="" AMF_ORDER_NUM="" IR_NUM="GB2003-2120-85049" CCN="GB_IC1A" CUSTOMER_NUM="GB90117414" CUSTOMER_BUID="202" ORDER_DATE="2019-09-30T07:59:32" COUNTRY_NUM="GB" CCN_DESC="GB Indirect Certified  Tier 1A"/>
  </ns0:CreateOrder>
</ns0:CreateSODSOrder>')
           COLUMNS 
           ORDER_NUM      VARCHAR2(30)    PATH '@ORDER_NUM',
           DPID           VARCHAR2(30)    PATH '@DPID');
BEGIN  
FOR rec_ IN get_data LOOP
  dbms_output.put_line(rec_.ORDER_NUM);
  dbms_output.put_line(rec_.DPID);
  END LOOP;
END;