Please help to read the XML document with XMLTable

user12021633 · Mar 5, 2015 11:33AM

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"?>

<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">

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location />

<JrValidationBreedName>Beefmaster</JrValidationBreedName>

<ValidatedBy>laineyb</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

<AnimalName>KPH PURPLE CORALEE 349</AnimalName>

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

<NameofDam>TCC CORKY 6603</NameofDam>

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

<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>

<NameofDam>LANGFORDS LADY 2206 ET</NameofDam>

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location />

<JrValidationBreedName>Hereford</JrValidationBreedName>

<ValidatedBy>Validator</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

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

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location />

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

<NameofSire>RST TIME'S A WASTIN' 0124</NameofSire>

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

</DocumentElement>

</diffgr:diffgram>

</DataTable>

odie_63 · Mar 8, 2015 8:55AM

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

odie_63 · 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 · 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 · Mar 5, 2015 6:08PM

Hi Oldie_63

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

odie_63 · Mar 6, 2015 5:37AM

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 · 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

<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">

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location/>

<JrValidationBreedName>Beefmaster</JrValidationBreedName>

<ValidatedBy>laineyb</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

<AnimalName>KPH PURPLE CORALEE 349</AnimalName>

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

<NameofDam>TCC CORKY 6603</NameofDam>

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

<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>

<NameofDam>LANGFORDS LADY 2206 ET</NameofDam>

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location/>

<JrValidationBreedName>Hereford</JrValidationBreedName>

<ValidatedBy>Validator</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

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

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location/>

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

<ClubName>Perrin FFA</ClubName>

<LastName>Hamman</LastName>

<FirstName>Kaily</FirstName>

<Email>[email protected]</Email>

<Exhibitor>Hamman, Kaily</Exhibitor>

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

<NameofSire>RST TIME'S A WASTIN' 0124</NameofSire>

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

<Tattoo_Location>TATTOO - Left Ear</Tattoo_Location>

<Secondary_Tattoo_Location>TATTOO - Right Ear</Secondary_Tattoo_Location>

<JrValidationBreedName>Polled Hereford</JrValidationBreedName>

<ValidatedBy>Hannah</ValidatedBy>

</JrShowCustomerHeifers>

</DocumentElement>

</diffgr:diffgram>

</GetJrShowCustomerHeifersResult>

</GetJrShowCustomerHeifersResponse>

Thanks again.

odie_63 · Mar 8, 2015 5:43AM

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 · 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 · Mar 8, 2015 8:55AM

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

Forum Stats

Quick Links

Categories

Please help to read the XML document with XMLTable

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Please help to read the XML document with XMLTable

Best Answer

Answers