Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

What IR_ROWFILTER syntax should look like?

GosforthMar 9 2021 — edited Mar 9 2021

Hi,
for single filter I have syntax:
'f?p=&APP_ID.:15:&SESSION.:OFFER::RIR,CIR:IR_DETAILS:27'
But what if I need to use ROWFILTER with OR operator?
In filter Interface I create such filter by typing
P='40' OR P='27' OR ...
But in URL (column has name 'x_column')?
'f?p=&APP_ID.:15:&SESSION.:OFFER::RIR,CIR:???'
Regards,
G.
This post has been answered by fac586 on Mar 9 2021
Jump to Answer

Comments

odie_63

You've almost got it right, just a little namespace issue.

Given the way you've declared the namespaces, the XQuery expression should be : 

'/dt:DataTable/dg:diffgram/DocumentElement/JrShowCustomerHeifers'
user12021633

Hi Oldie_63

Thanks for your help. I have one more question if you can help me out.

It is working as expected. No problem to get all the values

But for some reason value of date of birth does not showup, if I want to get the following way

FOR v_all IN ( SELECT exh.docid,exh.clubname,exh.exhibitor,exh.animalname,exh.RegNo,exh.dob

   FROM XMLTABLE(xmlnamespaces(

     'http://tempuri.org/' as "dt",

     'urn:schemas-microsoft-com:xml-diffgram-v1' as "dg"),

    'for $i in /dt:DataTable/dg:diffgram/DocumentElement/JrShowCustomerHeifers   

    return <Details>

     {$i/DocumentID}

     {$i/ClubName}

     {$i/Email}

     {$i/Exhibitor}

     {$i/AnimalName}

     {$i/RegistryNo}

     {$i/DateOfBirth}   

     </Details>'

    PASSING  XMLTYPE(l_clob)

    COLUMNS

    DocID  NUMBER  PATH 'DocumentID',

    ClubName VARCHAR2(100) PATH 'ClubName',

    Email  VARCHAR2(100) PATH 'Email',

    Exhibitor VARCHAR2(100) PATH 'Exhibitor',

    AnimalName VARCHAR2(100) PATH 'AnimalName', 

    RegNo  VARCHAR2(100) PATH 'RegistryNo',

    Dob  VARCHAR2(100) PATH 'DateofBirth' -- Trying to bring as Varchar2 instead of DATE

    ) as exh

   )

LOOP

  dbms_output.put_line('DocID = '||v_all.DocID||' Club = '||v_all.ClubName);

  dbms_output.put_line(' Exh = '||v_all.exhibitor||' DOB = '||v_all.DOB);

  dbms_output.put_line(' Animal = '||v_all.animalName||' RegNo = '||v_all.regno);

  dbms_output.put_line(chr(10));

END LOOP;

Thanks in advance.

user12021633


Hi Oldie_63

Thanks for your help. It is working now. I have made a type mistake.

odie_63

Why use an XQuery FLWOR expression now? There's no value added in doing so, it just complicates the query unnecessarily.

(PS: Please mark the thread as answered if you have no more questions)

user12021633

Hi odie_63,

Good evening. I am not sure what you are suggesting. Please provide me the example how to solve the problem. I am very new to XML parsing.

In the meantime, I am having another problem to get the data because .net developer changes the webservice format. To me it is supposed to work fine. May be my eyes are getting blurred so, I can't see where I am wrong.

Thanks in advance. Please help me out.

SELECT exh.docid,exh.clubname,exh.lname,exh.fname,exh.exhibitor,exh.animalname

FROM util.hlsr_online_entries e,

XMLTABLE(

  xmlnamespaces(

   'http://webservices.hlsr.net/JrShowOracleService/' as "dt",

   'urn:schemas-microsoft-com:xml-diffgram-v1'  as "dg"),

  '/dt:GetJrShowCustomerHeifersResponse/GetJrShowCustomerHeifersResult/dg:diffgram/DocumentElement/JrShowCustomerHeifers'

  PASSING XMLTYPE(e.entry_data)

  COLUMNS

  SeqNo  FOR ORDINALITY,

  DocID  NUMBER  PATH 'DocumentID',

  ClubName VARCHAR2(100) PATH 'ClubName',

  LName  VARCHAR2(100) PATH 'LastName',

  FName  VARCHAR2(100) PATH 'FirstName',

  Email  VARCHAR2(100) PATH 'Email',

  Exhibitor VARCHAR2(100) PATH 'Exhibitor',

  AnimalName VARCHAR2(100) PATH 'AnimalName', 

  RegNo  VARCHAR2(100) PATH 'RegistryNo',

  DOB  VARCHAR2(100) PATH 'DateofBirth',

  SireName VARCHAR2(100) PATH 'NameofSire',

  SireRegNo VARCHAR2(100) PATH 'SireRegistryNo',

  Dam  VARCHAR2(100) PATH 'NameofDam',

  DamRegNo VARCHAR2(100) PATH 'DamRegistryNo',

  Tattoo  VARCHAR2(100) PATH 'Tattoo',

  SecTattoo VARCHAR2(100) PATH 'SecondaryTattoo',

  UnivIDNum VARCHAR2(100) PATH 'UniversalIDNumber',

  TattooLoc VARCHAR2(100) PATH 'Tattoo_Location',

  OraBreedID NUMBER  PATH 'OracleBreedID',

  Breed  VARCHAR2(100) PATH 'JrValidationBreedName',

  ValDate  VARCHAR2(100) PATH 'ValidationDate'

  ) as exh

WHERE e.ref_id = 33432

<GetJrShowCustomerHeifersResponse xmlns="http://webservices.hlsr.net/JrShowOracleService/">

  <GetJrShowCustomerHeifersResult>

    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

      <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="JrShowCustomerHeifers" msdata:UseCurrentLocale="true">

        <xs:complexType>

          <xs:choice minOccurs="0" maxOccurs="unbounded">

            <xs:element name="JrShowCustomerHeifers">

              <xs:complexType>

                <xs:sequence>

                  <xs:element name="DocumentID" type="xs:int" minOccurs="0"/>

                  <xs:element name="ClubName" type="xs:string" minOccurs="0"/>

                  <xs:element name="LastName" type="xs:string" minOccurs="0"/>

                  <xs:element name="FirstName" type="xs:string" minOccurs="0"/>

                  <xs:element name="PreferredName" type="xs:string" minOccurs="0"/>

                  <xs:element name="Email" type="xs:string" minOccurs="0"/>

                  <xs:element name="Exhibitor" type="xs:string" minOccurs="0"/>

                  <xs:element name="AnimalName" type="xs:string" minOccurs="0"/>

                  <xs:element name="RegistryNo" type="xs:string" minOccurs="0"/>

                  <xs:element name="DateofBirth" type="xs:string" minOccurs="0"/>

                  <xs:element name="NameofSire" type="xs:string" minOccurs="0"/>

                  <xs:element name="SireRegistryNo" type="xs:string" minOccurs="0"/>

                  <xs:element name="NameofDam" type="xs:string" minOccurs="0"/>

                  <xs:element name="DamRegistryNo" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tattoo" type="xs:string" minOccurs="0"/>

                  <xs:element name="SecondaryTattoo" type="xs:string" minOccurs="0"/>

                  <xs:element name="UniversalIDNumber" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tattoo_Location" type="xs:string" minOccurs="0"/>

                  <xs:element name="Secondary_Tattoo_Location" type="xs:string" minOccurs="0"/>

                  <xs:element name="OracleBreedID" type="xs:int" minOccurs="0"/>

                  <xs:element name="JrValidationBreedName" type="xs:string" minOccurs="0"/>

                  <xs:element name="ValidationDate" type="xs:string" minOccurs="0"/>

                  <xs:element name="ValidatedBy" type="xs:string" minOccurs="0"/>

                  <xs:element name="ValidationComment" type="xs:string" minOccurs="0"/>

                </xs:sequence>

              </xs:complexType>

            </xs:element>

          </xs:choice>

        </xs:complexType>

      </xs:element>

    </xs:schema>

    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

      <DocumentElement xmlns="">

        <JrShowCustomerHeifers diffgr:id="JrShowCustomerHeifers1" msdata:rowOrder="0">

          <DocumentID>18442</DocumentID>

          <ClubName>Perrin FFA</ClubName>

          <LastName>Hamman</LastName>

          <FirstName>Kaily</FirstName>

          <Email>bshamman@wf.net</Email>

          <Exhibitor>Hamman, Kaily</Exhibitor>

          <AnimalName>113</AnimalName>

          <RegistryNo>C1026447</RegistryNo>

          <DateofBirth>01/14/2013</DateofBirth>

          <NameofSire>808 GAME DAY 808 LH</NameofSire>

          <SireRegistryNo>C961101</SireRegistryNo>

          <NameofDam>SADDIE 7/7 LE</NameofDam>

          <DamRegistryNo>C941067</DamRegistryNo>

          <Tattoo>113</Tattoo>

          <SecondaryTattoo/>

          <UniversalIDNumber>1194F020</UniversalIDNumber>

          <Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

          <Secondary_Tattoo_Location/>

          <OracleBreedID>6383</OracleBreedID>

          <JrValidationBreedName>Beefmaster</JrValidationBreedName>

          <ValidationDate>11/25/2014</ValidationDate>

          <ValidatedBy>laineyb</ValidatedBy>

          <ValidationComment/>

        </JrShowCustomerHeifers>

        <JrShowCustomerHeifers diffgr:id="JrShowCustomerHeifers2" msdata:rowOrder="1">

          <DocumentID>18473</DocumentID>

          <ClubName>Perrin FFA</ClubName>

          <LastName>Hamman</LastName>

          <FirstName>Kaily</FirstName>

          <Email>bshamman@wf.net</Email>

          <Exhibitor>Hamman, Kaily</Exhibitor>

          <AnimalName>KPH PURPLE CORALEE 349</AnimalName>

          <RegistryNo>P43461953</RegistryNo>

          <DateofBirth>11/04/2013</DateofBirth>

          <NameofSire>PURPLE MOXY 22X ET</NameofSire>

          <SireRegistryNo>P43126458</SireRegistryNo>

          <NameofDam>TCC CORKY 6603</NameofDam>

          <DamRegistryNo>P42457119</DamRegistryNo>

          <Tattoo>349</Tattoo>

          <SecondaryTattoo>KPH</SecondaryTattoo>

          <UniversalIDNumber>1194F021</UniversalIDNumber>

          <Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

          <Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

          <OracleBreedID>6389</OracleBreedID>

          <JrValidationBreedName>Polled Hereford</JrValidationBreedName>

          <ValidationDate>12/01/2014</ValidationDate>

          <ValidatedBy>Hannah</ValidatedBy>

          <ValidationComment/>

        </JrShowCustomerHeifers>

        <JrShowCustomerHeifers diffgr:id="JrShowCustomerHeifers3" msdata:rowOrder="2">

          <DocumentID>18474</DocumentID>

          <ClubName>Perrin FFA</ClubName>

          <LastName>Hamman</LastName>

          <FirstName>Kaily</FirstName>

          <Email>bshamman@wf.net</Email>

          <Exhibitor>Hamman, Kaily</Exhibitor>

          <AnimalName>LANGFORDS SWEET N SOUR 4107</AnimalName>

          <RegistryNo>43504761</RegistryNo>

          <DateofBirth>03/02/2014</DateofBirth>

          <NameofSire>LH TNT 1017</NameofSire>

          <SireRegistryNo>43199794</SireRegistryNo>

          <NameofDam>LANGFORDS LADY 2206 ET</NameofDam>

          <DamRegistryNo>43315143</DamRegistryNo>

          <Tattoo>4107</Tattoo>

          <SecondaryTattoo/>

          <UniversalIDNumber>1194F018</UniversalIDNumber>

          <Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

          <Secondary_Tattoo_Location/>

          <OracleBreedID>6398</OracleBreedID>

          <JrValidationBreedName>Hereford</JrValidationBreedName>

          <ValidationDate>11/24/2014</ValidationDate>

          <ValidatedBy>Validator</ValidatedBy>

          <ValidationComment/>

        </JrShowCustomerHeifers>

        <JrShowCustomerHeifers diffgr:id="JrShowCustomerHeifers4" msdata:rowOrder="3">

          <DocumentID>18475</DocumentID>

          <ClubName>Perrin FFA</ClubName>

          <LastName>Hamman</LastName>

          <FirstName>Kaily</FirstName>

          <Email>bshamman@wf.net</Email>

          <Exhibitor>Hamman, Kaily</Exhibitor>

          <AnimalName>PURPLE CCC LYDIA 19A</AnimalName>

          <RegistryNo>P43406978</RegistryNo>

          <DateofBirth>02/05/2013</DateofBirth>

          <NameofSire>PURPLE MB WOMANIZER 14UET</NameofSire>

          <SireRegistryNo>P42945146</SireRegistryNo>

          <NameofDam>PURPLE CMCC NASTIA 9U</NameofDam>

          <DamRegistryNo>P42927201</DamRegistryNo>

          <Tattoo>19A</Tattoo>

          <SecondaryTattoo/>

          <UniversalIDNumber>1194F017</UniversalIDNumber>

          <Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

          <Secondary_Tattoo_Location/>

          <OracleBreedID>6389</OracleBreedID>

          <JrValidationBreedName>Polled Hereford</JrValidationBreedName>

          <ValidationDate>12/01/2014</ValidationDate>

          <ValidatedBy>Hannah</ValidatedBy>

          <ValidationComment/>

        </JrShowCustomerHeifers>

        <JrShowCustomerHeifers diffgr:id="JrShowCustomerHeifers5" msdata:rowOrder="4">

          <DocumentID>18477</DocumentID>

          <ClubName>Perrin FFA</ClubName>

          <LastName>Hamman</LastName>

          <FirstName>Kaily</FirstName>

          <Email>bshamman@wf.net</Email>

          <Exhibitor>Hamman, Kaily</Exhibitor>

          <AnimalName>PURPLE SGW EDEN 12B</AnimalName>

          <RegistryNo>P43521932</RegistryNo>

          <DateofBirth>04/02/2014</DateofBirth>

          <NameofSire>RST TIME&apos;S A WASTIN&apos; 0124</NameofSire>

          <SireRegistryNo>43123163</SireRegistryNo>

          <NameofDam>PURPLE SM WONDER WOMAN 160Y</NameofDam>

          <DamRegistryNo>P43235169</DamRegistryNo>

          <Tattoo>12B</Tattoo>

          <SecondaryTattoo>12B</SecondaryTattoo>

          <UniversalIDNumber>1194F015</UniversalIDNumber>

          <Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

          <Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

          <OracleBreedID>6389</OracleBreedID>

          <JrValidationBreedName>Polled Hereford</JrValidationBreedName>

          <ValidationDate>12/01/2014</ValidationDate>

          <ValidatedBy>Hannah</ValidatedBy>

          <ValidationComment/>

        </JrShowCustomerHeifers>

      </DocumentElement>

    </diffgr:diffgram>

  </GetJrShowCustomerHeifersResult>

</GetJrShowCustomerHeifersResponse>

Thanks again.

odie_63

Hi,

I am not sure what you are suggesting.

I was just commenting on your previous query where you used this :

for $i in /dt:DataTable/dg:diffgram/DocumentElement/JrShowCustomerHeifers  

return <Details>

     {$i/DocumentID}

     {$i/ClubName}

     {$i/Email}

     {$i/Exhibitor}

     {$i/AnimalName}

     {$i/RegistryNo}

     {$i/DateOfBirth}  

  </Details>

pointing out the fact that a FLWOR expression like that is not necessary in this case, a simple XPath is fine enough and simpler to write (which you used eventually).

FLWOR stands for "For Let Where Order-by Return", and designates the full form of an XQuery query expression.

May be my eyes are getting blurred so, I can't see where I am wrong.

Maybe

You're missing a namespace prefix again :

/dt:GetJrShowCustomerHeifersResponse/dt:GetJrShowCustomerHeifersResult/dg:diffgram/DocumentElement/JrShowCustomerHeifers
user12021633

Hi Odie_63,

Good morning. Thanks again. You are the guru. It is working as magic. Yesterday I was looking at the code so long so I guess I overlooked the prefix. It is better to have a couple of eyes to pinpoint the problem.

In terms of your suggestion, I have never used the syntax FLWOR. I would try to implement if I can make it work.

"FLWOR stands for "For Let Where Order-by Return", and designates the full form of an XQuery query expression"

Do you think it would be faster than the way I am getting the values?

odie_63
Answer 
user12021633 wrote:




In terms of your suggestion, I have never used the syntax FLWOR. I would try to implement if I can make it work.




"FLWOR stands for "For Let Where Order-by Return", and designates the full form of an XQuery query expression"




Do you think it would be faster than the way I am getting the values?

You did use a FLWOR expression (the 'for' + 'return' part of it) in this post :

And what I already said is : do not use it in this case.

Faster or slower is not the point. Oracle will evaluate the expression the same way.

But from a maintainance point of view, it's obviously simpler to use a simple XPath expression like this :

/dt:GetJrShowCustomerHeifersResponse/dt:GetJrShowCustomerHeifersResult/dg:diffgram/DocumentElement/JrShowCustomerHeifers

Marked as Answer by user12021633 · Sep 27 2020
1 person found this helpful
1 - 8

Post Details

Added on Mar 9 2021
#apex-discussions, #filter
2 comments
110 views