2 Replies Latest reply: Nov 15, 2012 7:04 AM by AlexAnd RSS

    XML : Parameter passing

    Jitesh Gurnani
      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

      Edited by: Jitesh Gurnani on Nov 15, 2012 10:31 PM
        • 1. Re: XML : Parameter passing
          odie_63
          Hi,

          Please explain what you want to do with a test case (sample data etc.).

          What's the database version?
          • 2. Re: XML : Parameter passing
            AlexAnd
            any example what do you have and what do you want

            as example
            SQL> with t as
              2  (
              3  select 1 id from dual union all
              4  select 2 id from dual
              5  )
              6  select xmlelement("Id", id) from t
              7  /
             
            XMLELEMENT("ID",ID)
            --------------------------------------------------------------------------------
            <Id>1</Id>
            <Id>2</Id>
             
            SQL> 
            SQL> 
            SQL> with t as
              2  (
              3  select 1 id from dual union all
              4  select 2 id from dual
              5  )
              6  select xmlelement("root", xmlagg(xmlelement("Id", id))) from t
              7  /
             
            XMLELEMENT("ROOT",XMLAGG(XMLEL
            --------------------------------------------------------------------------------
            <root><Id>1</Id><Id>2</Id></root>
             
            SQL>