Want fetch a XML node from Oracle table

Question

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

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('
    
    
        
            
                Mirac
                12
                Finance
            
        
    

').EXTRACT('//Body/ns2:handleEmploye/ns2:reisterEmp/ns4:empId/text()').getStringVal() result
FROM dual;

Can any one suggest?

user_266304 · Answer

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

Want fetch a XML node from Oracle table

1 Answers