DevOps, CI/CD and Automation

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'

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.

Hi Oldie_63

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

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)

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.

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

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?