Want fetch a XML node from Oracle table-CodePudding

In the oracle table there is a column that stores XML below is the value:

    <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>

Now i am trying to fetch empId but getting below error:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing

I used ref. from this link but it doesn't work for me.

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;

Can any one suggest?

CodePudding user response：

You've mentioned ORA-31011 and ORA-19202, but with what you've shown those will be caused by LPX-00601:

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()'

which is because your XML and XPath have namespace references (which look a bit confused in your modified XML), but you haven't included a namespace map in your extract() call:

SELECT XMLTYPE('...
').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

You could use XMLQuery instead, and supply the namespaces within the XPath:

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('...')
  RETURNING CONTENT) AS result
FROM dual;

RESULT
12

and then convert the result to a number if you want.

Or if you actually plan to get all the values out you could use XMLTable, and supply the namespaces with XMLNamespaces; but because you define 'ns4' differently for each node (in this example anyway) you either need to give them unique names for the query:

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('...')
  COLUMNS
    empId number PATH '//ns4id:empId',
    empName varchar2(30) PATH '//ns4name:empName',
    empDep varchar2(30) PATH '//ns4dep:empDep'
);

EMPID	EMPNAME	EMPDEP
12	Mirac	Finance

or you could wildcard them all:

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('...')
  COLUMNS
    empId number PATH '//*:empId',
    empName varchar2(30) PATH '//*:empName',
    empDep varchar2(30) PATH '//*:empDep'
);

EMPID	EMPNAME	EMPDEP
12	Mirac	Finance

db<>fiddle

CodePudding user response：

your XML is badly formed?. There is no opening tag in the posted example (there is in your manual select)