Forum Stats

  • 3,854,668 Users
  • 2,264,397 Discussions
  • 7,905,749 Comments

Discussions

Please help to read the XML document with XMLTable

user12021633
user12021633 Member Posts: 12
edited Mar 8, 2015 8:55AM in XQuery

Hi Gurus,

I am not very familiar with XML parsing. It seems to me it should be very easy to get the data. For some reason, I am having a problem to get the data.

SELECT *
FROM util.hlsr_online_entries e,
XMLTABLE(
  xmlnamespaces(
   'http://tempuri.org/'    as "dt",
   'urn:schemas-microsoft-com:xml-diffgram-v1' as "dg"),

  '/DataTable/dg:diffgram/DocumentElement/JrShowCustomerHeifers'
  PASSING XMLTYPE(e.entry_data)
  COLUMNS
  SeqNo  FOR ORDINALITY,
  DocumentID NUMBER  PATH 'DocumentID',
  ClubName VARCHAR2(100) PATH 'ClubName') as test
WHERE e.ref_id = 33422

The above query does not bring any data for me. My hunts is the problem with DocumentElement tab. I have been trying a different variation to handle.

Please help me out to tune the query

I have the following XML document from the DotNet developer

<?xml version="1.0" encoding="utf-8"?>

<DataTable xmlns="http://tempuri.org/">

  <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:dateTime" 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>[email protected]</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>2014-11-25T08:39:00-06:00</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>[email protected]</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>2014-12-01T11:55:00-06:00</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>[email protected]</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>2014-11-24T14:26:00-06:00</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>[email protected]</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>2014-12-01T11:55:00-06:00</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>[email protected]</Email>

        <Exhibitor>Hamman, Kaily</Exhibitor>

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

        <RegistryNo>P43521932</RegistryNo>

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

        <NameofSire>RST TIME'S A WASTIN' 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>2014-12-01T11:56:00-06:00</ValidationDate>

        <ValidatedBy>Hannah</ValidatedBy>

        <ValidationComment />

      </JrShowCustomerHeifers>

    </DocumentElement>

  </diffgr:diffgram>

</DataTable>

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 8, 2015 8:55AM 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

    user12021633

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 5, 2015 1:13PM

    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
    user12021633 Member Posts: 12
    edited Mar 5, 2015 5:54PM

    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
    user12021633 Member Posts: 12
    edited Mar 5, 2015 6:08PM


    Hi Oldie_63

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

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 6, 2015 5:39AM

    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
    user12021633 Member Posts: 12
    edited Mar 7, 2015 8:09PM

    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>[email protected]</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>[email protected]</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>[email protected]</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>[email protected]</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>[email protected]</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
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 8, 2015 5:44AM

    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
    user12021633 Member Posts: 12
    edited Mar 8, 2015 7:52AM

    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
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Mar 8, 2015 8:55AM 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

    user12021633
This discussion has been closed.