Skip to Main Content
Forums

DevOps, CI/CD and Automation

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!

Please help to read the XML document with XMLTable

user12021633Mar 5 2015 — edited Mar 8 2015

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>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>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>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>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>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>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>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>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>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'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>
This post has been answered by odie_63 on Mar 8 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2015
Added on Mar 5 2015
#xml, #xquery
8 comments
3,981 views