This discussion is archived
2 Replies Latest reply: Nov 16, 2012 4:52 AM by AlexAnd RSS

XML query

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
  • 1. Re: XML query
    AlexAnd Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

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