2 Replies Latest reply: Nov 16, 2012 6:52 AM by AlexAnd RSS

    XML query

    Jitesh Gurnani-Oracle
      Hi All,

      I would wish to pass a number as a parameter to the XML query.

      For example:

      <Id>4</Id> where 4 is the parameter.

      Now I know this works but I need a solution such that I could pass different values in a loop as parameter to the XML query.

      The query works fine when I pass a single parameter but if I try to pass a number of parameters in a loop or using with clause, the query retrieves unwanted result set.

      Could you please help me with the same.

      Example:

      with T as
      (
      select 1 id from dual union all
      select 2 id from dual
      )
      SELECT
      EXTRACTVALUE(VALUE(SR), '/Activity/empid','xmlns="urn:/crmondemand/xml/activity"') AS ACCOUNTNAME,
      EXTRACTVALUE(VALUE(SR), '/Activity/empname','xmlns="urn:/crmondemand/xml/activity"') AS SUBJECT,
      FROM T,
      TABLE(XMLSEQUENCE(EXTRACT(
      WEB_SERVICE('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="urn:crmondemand/ws/activity/10/2004" xmlns:act="urn:/crmondemand/xml/activity">
      <soapenv:Header/>
      <soapenv:Body>
      <ns:ActivityNWS_Activity_QueryPage_Input>
      <PageSize>100</PageSize>
      <act:ListOfActivity>
      <act:Activity>
      <empid> T.id </empid>
      <empname />
      </act:Activity>
      </act:ListOfActivity>
      <ns:StartRowNum>0</ns:StartRowNum>
      </ns:ActivityNWS_Activity_QueryPage_Input>
      </soapenv:Body>
      </soapenv:Envelope>'
      ,'document/urn:crmondemand/ws/activity/10/2004:Activity_QueryPage',:GLOBAL_SID),
      '/soap:Envelope/soap:Body/*/*/*','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/'))) SR

      I want to pass the values in the with clause (T.id) as parameter to the query. Also this T.id is a number.

      Could you please help with this.

      Regards,
      Jitesh
        • 1. Re: XML query
          AlexAnd
          plz try to solve your problem in one thread
          duplicate - XML : Parameter passing


          >
          I want to pass the values in the with clause (T.id) as parameter to the query.
          >
          query? where?

          in my opinion you mix different things

          >
          <Id>4</Id> where 4 is the parameter.

          Now I know this works but I need a solution such that I could pass different values in a loop as parameter to the XML query.
          >
          how this work?
          post sample plz
          • 2. Re: XML query
            odie_63
            Use concatenation to include ID value in the SOAP request :
            with T as
            (
            select 1 id from dual union all
            select 2 id from dual
            )
            SELECT EXTRACTVALUE(VALUE(SR), '/Activity/empid','xmlns="urn:/crmondemand/xml/activity"') AS ACCOUNTNAME,
                   EXTRACTVALUE(VALUE(SR), '/Activity/empname','xmlns="urn:/crmondemand/xml/activity"') AS SUBJECT,
            FROM T,
                 TABLE(
                   XMLSEQUENCE(
                     EXTRACT(
                       WEB_SERVICE(
            '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="urn:crmondemand/ws/activity/10/2004" xmlns:act="urn:/crmondemand/xml/activity">
            <soapenv:Header/>
            <soapenv:Body>
            <ns:ActivityNWS_Activity_QueryPage_Input>
            <PageSize>100</PageSize>
            <act:ListOfActivity>
            <act:Activity>
            <empid>' || T.id || '</empid>
            <empname />
            </act:Activity>
            </act:ListOfActivity>
            <ns:StartRowNum>0</ns:StartRowNum>
            </ns:ActivityNWS_Activity_QueryPage_Input>
            </soapenv:Body>
            </soapenv:Envelope>'
                      ,'document/urn:crmondemand/ws/activity/10/2004:Activity_QueryPage',:GLOBAL_SID),
                     '/soap:Envelope/soap:Body/*/*/*',
                     'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/'
                     )
                   )
                 ) SR
            ;
            {code}