0

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.

  • Hi, Sorry for not mentioning am using Oracle, unable to add same in tag. – Irfan Sep 18 '18 at 14:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.