I've search the discussion groups looking for answers and I'm at a loss. I've tried implementing solutions that have worked, but I'm still seeing NULLs returning. I have a table that has a CLOB column called APPLICATION_DATA. Below is an example of the format of the XML:
So I'm trying to get the contents of Question Name = emailFailedSendToPortal and it's returning NULL. I've tried writing the following statement:
SELECT XMLCast(
XMLQuery(
'declare default element namespace "http://ws.compas.aarp.uhg.com/common/1/4/0/base"; (: :)
declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/"; (: :)
declare namespace wsu = "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"; (: :)
declare namespace ns2 = "http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/enrollmentservice"; (: :)
declare namespace ns3 = "http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/common"; (: :)
declare namespace ns4 = "http://ws.compas.aarp.uhg.com/common/1/3/0/employer"; (: :)
declare namespace ns5 = "http://ws.compas.aarp.uhg.com/exception/1/0/0/fault"; (: :)
/ns2:ApplicationInformation/ns3:Questions/QuestionList [@Type="BBA"]/Question [@name="emailFailedSendToPortal"]/ns3:text'
PASSING APPLICATION_DATA RETURNING CONTENT)
as varchar2(80)
) as node_value
FROM OLE_APPLICATION_DETAIL
fetch first 10 rows only
Am I missing something? Are the namespace declarations correct? Any help would be greatly appreciated. TIA!