Help – SQL query // Extracting from XML // getting error if no data found

oraclexml

I am building a query, where I am extract values from XML. I am getting output if the xml has values. But if no xml found am getting error I need help to fill those columns with "yet to get" or something.

below is sample query:

Select Column1,Column2,Column3,Column4,
       extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/') "Response",
Extractvalue(Xmltype(Responsemsg)
From Table.Name
where column5 is not null;

Note: if I give Column5 is not null, I am getting output but I need output without "column5 is not null" also.

Best Answer

Have you tried checking if column5 is NULL in the SELECT list instead of in the WHERE clause? I presume that feeding NULL to xmltype() is the source of your errors.

Select Column1,Column2,Column3,Column4,
       CASE WHEN column5 IS NOT NULL
         THEN extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/')
         ELSE 'No Value Yet'
       END as "Response",
       Extractvalue(Xmltype(Responsemsg))
From Table.Name
where column5 is not null;

I've also added a closing parenthesis to the sixth column in the SELECT list; you may want to give that column an alias as well.

Note: not a frequent Oracle user, and have no experience with their XML tools; just applying standard SQL. Also, of course, untested.

Related Question