Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="http://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
').EXTRACT('//Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()').getStringVal() result
FROM dual;
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()'

SELECT XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="http://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
').EXTRACT('//Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()',
  'xmlns="http://load.xyx.gen/" xmlns:ns2="http://load.xyx.gen/emp1" xmlns:ns4="http://load.xyx.gen/empId1"'
  ).getStringVal() result
FROM dual;


RESULT


12


-- basic XMLQuery, with namespaces
SELECT XMLQuery(
 'declare namespace ns2="http://load.xyx.gen/emp1";
  declare namespace ns4="http://load.xyx.gen/empId1";
  declare default element namespace "http://load.xyx.gen/";
  //Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  RETURNING CONTENT) AS result
FROM dual;


RESULT


12


-- XMLQuery with result converted to string then number
SELECT to_number(XMLQuery(
 'declare namespace ns2="http://load.xyx.gen/emp1";
  declare namespace ns4="http://load.xyx.gen/empId1";
  declare default element namespace "http://load.xyx.gen/";
  //Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  RETURNING CONTENT).getStringVal()) AS result
FROM dual;


RESULT


12


-- XMLTable getting just empID
SELECT empId
FROM XMLTable(
  XMLNamespaces(default 'http://load.xyx.gen/',
    'http://load.xyx.gen/emp1' as "ns2",
    'http://load.xyx.gen/empId1' as "ns4"),
  '//Body/ns2:handleEmploye/ns2:reisterEmp'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  COLUMNS
    empId number PATH '//ns4:empId'
);


EMPID


12


-- XMLTable getting three columns, but only matching one because of namespace issues
SELECT empId, empName, empDep
FROM XMLTable(
  XMLNamespaces(default 'http://load.xyx.gen/',
    'http://load.xyx.gen/emp1' as "ns2",
    'http://load.xyx.gen/empId1' as "ns4"),
  '//Body/ns2:handleEmploye/ns2:reisterEmp'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  COLUMNS
    empId number PATH '//ns4:empId',
    empName varchar2(30) PATH '//ns4:empName',
    empDep varchar2(30) PATH '//ns4:empDep'
);


EMPID
EMPNAME
EMPDEP


12
null
null


-- XMLTable getting three columns, with renamed namespaces
SELECT empId, empName, empDep
FROM XMLTable(
  XMLNamespaces(default 'http://load.xyx.gen/',
    'http://load.xyx.gen/emp1' as "ns2",
    'http://load.xyx.gen/empName1' as "ns4name",
    'http://load.xyx.gen/empId1' as "ns4id",
    'http://load.xyx.gen/empDep1' as "ns4dep"),
  '//Body/ns2:handleEmploye/ns2:reisterEmp'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  COLUMNS
    empId number PATH '//ns4id:empId',
    empName varchar2(30) PATH '//ns4name:empName',
    empDep varchar2(30) PATH '//ns4dep:empDep'
);


EMPID
EMPNAME
EMPDEP


12
Mirac
Finance


-- XMLTable getting three columns, with wildcard namespaces
SELECT empId, empName, empDep
FROM XMLTable(
  XMLNamespaces(default 'http://load.xyx.gen/',
    'http://load.xyx.gen/emp1' as "ns2",
    'http://load.xyx.gen/empId1' as "ns4"),
  '//Body/ns2:handleEmploye/ns2:reisterEmp'
  PASSING XMLTYPE('<soapenv:Envelope xmlns:soapenv="http://load.xyx.gen/">
    <Header xmlns="http://load.xyx.gen/gen1"/>
    <Body xmlns="http://load.xyx.gen/" xmlns:soapenv="hhttp://load.xyx.gen/" xmlns:xsi="http://load.xyx.gen/instance" xmlns:SOAP-ENV="http://load.xyx.gen/" xmlns:SOAP-ENC="http://load.xyx.gen/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <ns2:handleEmploye xmlns:ns2="http://load.xyx.gen/emp1">
            <ns2:reisterEmp>
                <ns4:empName xmlns:ns4="http://load.xyx.gen/empName1">Mirac</ns4:empName>
                <ns4:empId xmlns:ns4="http://load.xyx.gen/empId1">12</ns4:empId>
                <ns4:empDep xmlns:ns4="http://load.xyx.gen/empDep1">Finance</ns4:empDep>
            </ns2:reisterEmp>
        </ns2:handleEmploye>
    </Body>
</soapenv:Envelope>
')
  COLUMNS
    empId number PATH '//*:empId',
    empName varchar2(30) PATH '//*:empName',
    empDep varchar2(30) PATH '//*:empDep'
);


EMPID
EMPNAME
EMPDEP


12
Mirac
Finance