This discussion is archived
2 Replies Latest reply: Nov 15, 2012 5:04 AM by AlexAnd RSS

XML : Parameter passing

Jitesh Gurnani Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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> 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points