Forum Stats

  • 3,839,092 Users
  • 2,262,450 Discussions
  • 7,900,854 Comments

Discussions

XMLQuery is returning NULL values

timsburke
timsburke Member Posts: 5 Green Ribbon

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!

Comments

  • cormaco
    cormaco Member Posts: 1,953 Silver Crown
    edited Jun 30, 2022 5:08AM

    Please post your example data as text in the future, not as a screenshot.

    You left out the ns3 prefix for QuestionList and Question in your XPath.

    Also in Question[@name="emailFailedSendToPortal"] the attribute "name" must be written as "Name"

  • timsburke
    timsburke Member Posts: 5 Green Ribbon

    @cormaco ... Sorry about that. Here's the data:

    <ns2:ApplicationInformation xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

                                xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 

                                xmlns="http://ws.compas.aarp.uhg.com/common/1/4/0/base" 

                                xmlns:ns2="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/enrollmentservice" 

                                xmlns:ns3="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/common" 

                                xmlns:ns4="http://ws.compas.aarp.uhg.com/common/1/3/0/employer" 

                                xmlns:ns5="http://ws.compas.aarp.uhg.com/exception/1/0/0/fault">

     <ns3:FormInformation>

      <ns3:ApplicationReceivedDate>2022-06-20</ns3:ApplicationReceivedDate>

      <ns3:HashId>XYZ</ns3:HashId>

      <ns3:ComponentNumber>8675309</ns3:ComponentNumber>

      <ns3:RequestedEffectiveDate>2022-07-01</ns3:RequestedEffectiveDate>

      <ns3:MarketingInformation>

       <ns3:MarketingSourceCode>ABC</ns3:MarketingSourceCode>

      </ns3:MarketingInformation>

      <ns3:ApplicationSubmissionMethod>9</ns3:ApplicationSubmissionMethod>

     </ns3:FormInformation>

     <ns3:AgentInformation>

      <ns3:AgentSource/>

      <ns3:AgentName>

       <ns3:FirstName>Bob</ns3:FirstName>

       <ns3:LastName>Smith</ns3:LastName>

      </ns3:AgentName>

      <ns3:AgentPoliciesInForce>NONE</ns3:AgentPoliciesInForce>

      <ns3:AgentPoliciesSold>NONE</ns3:AgentPoliciesSold>

      <ns3:AgentPoliciesSoldIn5NotInForce>NONE</ns3:AgentPoliciesSoldIn5NotInForce>

     </ns3:AgentInformation>

     <ns3:VendorInformation>

      <ns3:vendorCode/>

      <ns3:vendorType/>

     </ns3:VendorInformation>

     <ns3:PersonInformation>

     </ns3:PersonInformation>

     <ns3:Plans>

      <ns3:PlanChoice1>ZZZ</ns3:PlanChoice1>

     </ns3:Plans>

     <ns3:Questions>

      <ns3:QuestionList Type="BBA">

       <ns3:Question Name="QRIndicator">

        <ns3:Text>no</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="SignatureSubmission">

        <ns3:Text>Text</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="PaymentChoice">

        <ns3:Text>online</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="planName">

        <ns3:Text>PlanZZZ</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="emailFailedSendToPortal">

        <ns3:Text>true</ns3:Text>

       </ns3:Question>

      </ns3:QuestionList>

      <ns3:QuestionList Type="CPAGA">

      </ns3:QuestionList>

     </ns3:Questions>

     <ns3:AppIndicators>

      <ns3:appIndicator Name="SignatureInd">yes</ns3:appIndicator>

      <ns3:appIndicator Name="CpaSignatureInd">yes</ns3:appIndicator>

      <ns3:appIndicator Name="PartABActiveIndicator">yes</ns3:appIndicator>

      <ns3:appIndicator Name="WritingAgentSignatureInd">yes</ns3:appIndicator>

     </ns3:AppIndicators>

     <ns3:SignatureDates>

      <ns3:CPASignatureDate>2022-06-20</ns3:CPASignatureDate>

      <ns3:SignatureDate>2022-06-20</ns3:SignatureDate>

     </ns3:SignatureDates>

    </ns2:ApplicationInformation>


    I made the change you suggested and it still returned NULLs

    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/ns3:QuestionList [@Type="BBA"]/ns3:Question [@Name="emailFailedSendToPortal"]/ns3:Text'

       PASSING APPLICATION_DATA RETURNING CONTENT)

      as varchar2(80)

      ) as node_value

     FROM OLE_APPLICATION_DETAIL


    Anything else it could be? TIA

  • timsburke
    timsburke Member Posts: 5 Green Ribbon

    @cormaco... Sorry about that. Here's the data:

    <ns2:ApplicationInformation xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

                                xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 

                                xmlns="http://ws.compas.aarp.uhg.com/common/1/4/0/base" 

                                xmlns:ns2="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/enrollmentservice" 

                                xmlns:ns3="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/common" 

                                xmlns:ns4="http://ws.compas.aarp.uhg.com/common/1/3/0/employer" 

                                xmlns:ns5="http://ws.compas.aarp.uhg.com/exception/1/0/0/fault">

     <ns3:FormInformation>

      <ns3:ApplicationReceivedDate>2022-06-20</ns3:ApplicationReceivedDate>

      <ns3:HashId>XYZ</ns3:HashId>

      <ns3:ComponentNumber>8675309</ns3:ComponentNumber>

      <ns3:RequestedEffectiveDate>2022-07-01</ns3:RequestedEffectiveDate>

      <ns3:MarketingInformation>

       <ns3:MarketingSourceCode>ABC</ns3:MarketingSourceCode>

      </ns3:MarketingInformation>

      <ns3:ApplicationSubmissionMethod>9</ns3:ApplicationSubmissionMethod>

     </ns3:FormInformation>

     <ns3:AgentInformation>

      <ns3:AgentSource/>

      <ns3:AgentName>

       <ns3:FirstName>Bob</ns3:FirstName>

       <ns3:LastName>Smith</ns3:LastName>

      </ns3:AgentName>

      <ns3:AgentPoliciesInForce>NONE</ns3:AgentPoliciesInForce>

      <ns3:AgentPoliciesSold>NONE</ns3:AgentPoliciesSold>

      <ns3:AgentPoliciesSoldIn5NotInForce>NONE</ns3:AgentPoliciesSoldIn5NotInForce>

     </ns3:AgentInformation>

     <ns3:VendorInformation>

      <ns3:vendorCode/>

      <ns3:vendorType/>

     </ns3:VendorInformation>

     <ns3:PersonInformation>

     </ns3:PersonInformation>

     <ns3:Plans>

      <ns3:PlanChoice1>ZZZ</ns3:PlanChoice1>

     </ns3:Plans>

     <ns3:Questions>

      <ns3:QuestionList Type="BBA">

       <ns3:Question Name="QRIndicator">

        <ns3:Text>no</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="SignatureSubmission">

        <ns3:Text>Text</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="PaymentChoice">

        <ns3:Text>online</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="planName">

        <ns3:Text>PlanZZZ</ns3:Text>

       </ns3:Question>

       <ns3:Question Name="emailFailedSendToPortal">

        <ns3:Text>true</ns3:Text>

       </ns3:Question>

      </ns3:QuestionList>

      <ns3:QuestionList Type="CPAGA">

      </ns3:QuestionList>

     </ns3:Questions>

     <ns3:AppIndicators>

      <ns3:appIndicator Name="SignatureInd">yes</ns3:appIndicator>

      <ns3:appIndicator Name="CpaSignatureInd">yes</ns3:appIndicator>

      <ns3:appIndicator Name="PartABActiveIndicator">yes</ns3:appIndicator>

      <ns3:appIndicator Name="WritingAgentSignatureInd">yes</ns3:appIndicator>

     </ns3:AppIndicators>

     <ns3:SignatureDates>

      <ns3:CPASignatureDate>2022-06-20</ns3:CPASignatureDate>

      <ns3:SignatureDate>2022-06-20</ns3:SignatureDate>

     </ns3:SignatureDates>

    </ns2:ApplicationInformation>


    I made the changes you suggested and it's still returning NULLs.

    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/ns3:QuestionList [@Type="BBA"]/ns3:Question [@Name="emailFailedSendToPortal"]/ns3:Text'

       PASSING APPLICATION_DATA RETURNING CONTENT)

      as varchar2(80)

      ) as node_value

     FROM OLE_APPLICATION_DETAIL


    Any other suggestions? TIA!

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy

    I used the XPath you listed in the above comment and it works for me. I had to make another change as you say the column is a CLOB, but you don't show where it gets converted to an XMLType so I did that on the PASSING line. I also removed all the extra namespaces as you only need to declare the namespaces used within the XPath itself.

    WITH OLE_APPLICATION_DETAIL AS
    (SELECT to_clob('<ns2:ApplicationInformation xmlns:ns2="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/enrollmentservice" xmlns:ns3="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/common">
        <ns3:Questions>
            <ns3:QuestionList Type="BBA">
                <ns3:Question Name="emailFailedSendToPortal">
                    <ns3:Text>true</ns3:Text>
                </ns3:Question>
            </ns3:QuestionList>
        </ns3:Questions>
    </ns2:ApplicationInformation>') APPLICATION_DATA from dual)
    -- Faking your table above
    SELECT XMLCast(
      XMLQuery(
         '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"; (: :)
       /ns2:ApplicationInformation/ns3:Questions/ns3:QuestionList [@Type="BBA"]/ns3:Question [@Name="emailFailedSendToPortal"]/ns3:Text'
       PASSING xmltype(APPLICATION_DATA) RETURNING CONTENT)
      as varchar2(80)
      ) as node_value
     FROM OLE_APPLICATION_DETAIL
    
  • timsburke
    timsburke Member Posts: 5 Green Ribbon

    I looked back at the table and the APPLICATION_DATA field's data type is XMLTYPE.

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,106 Silver Trophy

    Then my example simply becomes (moved the XMLType from your query into the WITH clause is all)

    WITH OLE_APPLICATION_DETAIL AS
    (SELECT xmltype('<ns2:ApplicationInformation xmlns:ns2="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/enrollmentservice" xmlns:ns3="http://ws.compas.aarp.uhg.com/enrollmentservice/4/5/0/common">
        <ns3:Questions>
            <ns3:QuestionList Type="BBA">
                <ns3:Question Name="emailFailedSendToPortal">
                    <ns3:Text>true</ns3:Text>
                </ns3:Question>
            </ns3:QuestionList>
        </ns3:Questions>
    </ns2:ApplicationInformation>') APPLICATION_DATA from dual)
    -- Faking your table above
    SELECT XMLCast(
      XMLQuery(
         '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"; (: :)
       /ns2:ApplicationInformation/ns3:Questions/ns3:QuestionList [@Type="BBA"]/ns3:Question [@Name="emailFailedSendToPortal"]/ns3:Text'
       PASSING APPLICATION_DATA RETURNING CONTENT)
      as varchar2(80)
      ) as node_value
     FROM OLE_APPLICATION_DETAIL
    
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy

    @timsburke,

    Your last attempt works for me as well, though it could be simplified a bit, as Jason showed.

    What's your database version? (select * from v$version)

  • timsburke
    timsburke Member Posts: 5 Green Ribbon

    Sorry for the delay in responding. The version is 19c. Jason's solution worked! Thanks everyone for your help!