You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Uma S Mudunuri <um...@yahoo.com> on 2005/08/31 16:22:17 UTC
problem retrieving xml results from oracle xmldb
Hi,
I am trying to use iBATIS to retrieve results from
Oracle's XML DB database(which stores whole xml
documents). It works fine when the results are the
text value of an element but when I try to use
functions to get whole or part of the xml document it
does not return any results. The query works fine when
executed directly in oracle. I am new to iBATIS and
have searched the archives and faq's but did not find
anything related.
An example xml file in the database
<employee>
<name>Scott</name>
<department>1</department>
</employee>
i) query to get name of the employee
<statement ....>
SELECT extractValue(value(x),'/employee/name') name
FROM EMPLOYEES
</statement>
This query works fine and the result is 'Scott'
ii) query to get the whole xml document
<statement ....>
SELECT extract(value(x),'/employee') xml
FROM employees
</statement>
does not return anything
I am guessing that the problem is because of the fact
that the result is an xml document and I would like to
know if there's any way to handle these queries in
iBATIS. Any help would be greatly appreciated.
Thanks,
Uma
____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
Re: problem retrieving xml results from oracle xmldb
Posted by Jan Vissers <Ja...@cumquat.nl>.
Yep, this works. But basically what I'm looking for is to have the
XMLType map to org.w3c.dom.Document in one go. Good suggestion though.
-J.
Mike Fagan wrote:
> When returning a oracle XMLType to ibatis use the
> getClobVal()/getStringVal() function and it work fine.
> That is why your extractValue works because it returns varchar2.
>
> so try
>
><statement ....>
> SELECT extract(value(x),'/employee').getClobVal() xml
> FROM employees
></statement>
>
>
> Mike Fagan
>
> Uma S Mudunuri wrote:
>
>>Thanks for the reply. Guess I have to go with the JDBC
>>implementation atleast for now.
>>
>>--- Jan Vissers <Ja...@cumquat.nl> wrote:
>>
>>
>>
>>>I'm guessing this has to do with the fact that
>>>iBatis doesn't support
>>>the Oracle XMLType datatype. I've also been looking
>>>for a way to deal
>>>with this, but more in the area of Custom Type
>>>Handling. Would be
>>>interested if someone has actually managed to do
>>>this. In my situation,
>>>due to time constraints we decided to implement
>>>XMLType operations as
>>>JDBC implemenation behind the iBatis DAO framework
>>>and do the rest with
>>>sqlMap implemantions.
>>>
>>>Uma S Mudunuri wrote:
>>>
>>>
>>>
>>>>Hi,
>>>>
>>>>I am trying to use iBATIS to retrieve results from
>>>>Oracle's XML DB database(which stores whole xml
>>>>documents). It works fine when the results are the
>>>>text value of an element but when I try to use
>>>>functions to get whole or part of the xml document
>>>>
>>>>
>>>it
>>>
>>>
>>>>does not return any results. The query works fine
>>>>
>>>>
>>>when
>>>
>>>
>>>>executed directly in oracle. I am new to iBATIS and
>>>>have searched the archives and faq's but did not
>>>>
>>>>
>>>find
>>>
>>>
>>>>anything related.
>>>>
>>>>An example xml file in the database
>>>>
>>>><employee>
>>>><name>Scott</name>
>>>><department>1</department>
>>>></employee>
>>>>
>>>>i) query to get name of the employee
>>>> <statement ....>
>>>> SELECT extractValue(value(x),'/employee/name')
>>>>
>>>>
>>>name
>>>
>>>
>>>> FROM EMPLOYEES
>>>> </statement>
>>>> This query works fine and the result is 'Scott'
>>>>
>>>>ii) query to get the whole xml document
>>>> <statement ....>
>>>> SELECT extract(value(x),'/employee') xml
>>>> FROM employees
>>>> </statement>
>>>>
>>>> does not return anything
>>>>
>>>>
>>>>I am guessing that the problem is because of the
>>>>
>>>>
>>>fact
>>>
>>>
>>>>that the result is an xml document and I would like
>>>>
>>>>
>>>to
>>>
>>>
>>>>know if there's any way to handle these queries in
>>>>iBATIS. Any help would be greatly appreciated.
>>>>
>>>>Thanks,
>>>>Uma
>>>>
>>>>
>>>>
>>>>
>>>>
>>>____________________________________________________
>>>
>>>
>>>>Start your day with Yahoo! - make it your home page
>>>>
>>>>
>>>>http://www.yahoo.com/r/hs
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>--
>>>Cumquat Information Technology
>>>De Dreef 19
>>>3706 BR Zeist
>>>T +31 (0)30 - 6940490
>>>F +31 (0)10 - 6940499
>>>http://www.cumquat.nl
>>>
>>>Jan.Vissers@cumquat.nl
>>>M +31 6 5 11 169 556
>>>
>>>
>>>
>>>
>>>
>>
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Tired of spam? Yahoo! Mail has the best spam protection around
>>http://mail.yahoo.com
>>
>>
>>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005
>
>
--
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)10 - 6940499
http://www.cumquat.nl
Jan.Vissers@cumquat.nl
M +31 6 5 11 169 556
Re: problem retrieving xml results from oracle xmldb
Posted by Uma S Mudunuri <um...@yahoo.com>.
Thanks Mike that solved my problem
--- Mike Fagan <mf...@tde.com> wrote:
> When returning a oracle XMLType to ibatis use the
> getClobVal()/getStringVal() function and it work
> fine.
> That is why your extractValue works because it
> returns varchar2.
>
> so try
>
> <statement ....>
> SELECT extract(value(x),'/employee').getClobVal()
> xml
> FROM employees
> </statement>
>
>
> Mike Fagan
>
> Uma S Mudunuri wrote:
>
> >Thanks for the reply. Guess I have to go with the
> JDBC
> >implementation atleast for now.
> >
> >--- Jan Vissers <Ja...@cumquat.nl> wrote:
> >
> >
> >
> >>I'm guessing this has to do with the fact that
> >>iBatis doesn't support
> >>the Oracle XMLType datatype. I've also been
> looking
> >>for a way to deal
> >>with this, but more in the area of Custom Type
> >>Handling. Would be
> >>interested if someone has actually managed to do
> >>this. In my situation,
> >>due to time constraints we decided to implement
> >>XMLType operations as
> >>JDBC implemenation behind the iBatis DAO framework
> >>and do the rest with
> >>sqlMap implemantions.
> >>
> >>Uma S Mudunuri wrote:
> >>
> >>
> >>
> >>>Hi,
> >>>
> >>>I am trying to use iBATIS to retrieve results
> from
> >>>Oracle's XML DB database(which stores whole xml
> >>>documents). It works fine when the results are
> the
> >>>text value of an element but when I try to use
> >>>functions to get whole or part of the xml
> document
> >>>
> >>>
> >>it
> >>
> >>
> >>>does not return any results. The query works fine
> >>>
> >>>
> >>when
> >>
> >>
> >>>executed directly in oracle. I am new to iBATIS
> and
> >>>have searched the archives and faq's but did not
> >>>
> >>>
> >>find
> >>
> >>
> >>>anything related.
> >>>
> >>>An example xml file in the database
> >>>
> >>><employee>
> >>><name>Scott</name>
> >>><department>1</department>
> >>></employee>
> >>>
> >>>i) query to get name of the employee
> >>> <statement ....>
> >>> SELECT extractValue(value(x),'/employee/name')
> >>>
> >>>
> >>name
> >>
> >>
> >>> FROM EMPLOYEES
> >>> </statement>
> >>> This query works fine and the result is 'Scott'
> >>>
> >>>ii) query to get the whole xml document
> >>> <statement ....>
> >>> SELECT extract(value(x),'/employee') xml
> >>> FROM employees
> >>> </statement>
> >>>
> >>> does not return anything
> >>>
> >>>
> >>>I am guessing that the problem is because of the
> >>>
> >>>
> >>fact
> >>
> >>
> >>>that the result is an xml document and I would
> like
> >>>
> >>>
> >>to
> >>
> >>
> >>>know if there's any way to handle these queries
> in
> >>>iBATIS. Any help would be greatly appreciated.
> >>>
> >>>Thanks,
> >>>Uma
> >>>
> >>>
> >>>
> >>>
> >>>
>
>>____________________________________________________
> >>
> >>
> >>>Start your day with Yahoo! - make it your home
> page
> >>>
> >>>
> >>>http://www.yahoo.com/r/hs
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>--
> >>Cumquat Information Technology
> >>De Dreef 19
> >>3706 BR Zeist
> >>T +31 (0)30 - 6940490
> >>F +31 (0)10 - 6940499
> >>http://www.cumquat.nl
> >>
> >>Jan.Vissers@cumquat.nl
> >>M +31 6 5 11 169 556
> >>
> >>
> >>
> >>
> >>
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Tired of spam? Yahoo! Mail has the best spam
> protection around
> >http://mail.yahoo.com
> >
> >
> >
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: problem retrieving xml results from oracle xmldb
Posted by Mike Fagan <mf...@tde.com>.
When returning a oracle XMLType to ibatis use the
getClobVal()/getStringVal() function and it work fine.
That is why your extractValue works because it returns varchar2.
so try
<statement ....>
SELECT extract(value(x),'/employee').getClobVal() xml
FROM employees
</statement>
Mike Fagan
Uma S Mudunuri wrote:
>Thanks for the reply. Guess I have to go with the JDBC
>implementation atleast for now.
>
>--- Jan Vissers <Ja...@cumquat.nl> wrote:
>
>
>
>>I'm guessing this has to do with the fact that
>>iBatis doesn't support
>>the Oracle XMLType datatype. I've also been looking
>>for a way to deal
>>with this, but more in the area of Custom Type
>>Handling. Would be
>>interested if someone has actually managed to do
>>this. In my situation,
>>due to time constraints we decided to implement
>>XMLType operations as
>>JDBC implemenation behind the iBatis DAO framework
>>and do the rest with
>>sqlMap implemantions.
>>
>>Uma S Mudunuri wrote:
>>
>>
>>
>>>Hi,
>>>
>>>I am trying to use iBATIS to retrieve results from
>>>Oracle's XML DB database(which stores whole xml
>>>documents). It works fine when the results are the
>>>text value of an element but when I try to use
>>>functions to get whole or part of the xml document
>>>
>>>
>>it
>>
>>
>>>does not return any results. The query works fine
>>>
>>>
>>when
>>
>>
>>>executed directly in oracle. I am new to iBATIS and
>>>have searched the archives and faq's but did not
>>>
>>>
>>find
>>
>>
>>>anything related.
>>>
>>>An example xml file in the database
>>>
>>><employee>
>>><name>Scott</name>
>>><department>1</department>
>>></employee>
>>>
>>>i) query to get name of the employee
>>> <statement ....>
>>> SELECT extractValue(value(x),'/employee/name')
>>>
>>>
>>name
>>
>>
>>> FROM EMPLOYEES
>>> </statement>
>>> This query works fine and the result is 'Scott'
>>>
>>>ii) query to get the whole xml document
>>> <statement ....>
>>> SELECT extract(value(x),'/employee') xml
>>> FROM employees
>>> </statement>
>>>
>>> does not return anything
>>>
>>>
>>>I am guessing that the problem is because of the
>>>
>>>
>>fact
>>
>>
>>>that the result is an xml document and I would like
>>>
>>>
>>to
>>
>>
>>>know if there's any way to handle these queries in
>>>iBATIS. Any help would be greatly appreciated.
>>>
>>>Thanks,
>>>Uma
>>>
>>>
>>>
>>>
>>>
>>____________________________________________________
>>
>>
>>>Start your day with Yahoo! - make it your home page
>>>
>>>
>>>http://www.yahoo.com/r/hs
>>>
>>>
>>>
>>>
>>>
>>>
>>--
>>Cumquat Information Technology
>>De Dreef 19
>>3706 BR Zeist
>>T +31 (0)30 - 6940490
>>F +31 (0)10 - 6940499
>>http://www.cumquat.nl
>>
>>Jan.Vissers@cumquat.nl
>>M +31 6 5 11 169 556
>>
>>
>>
>>
>>
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>
>
Re: problem retrieving xml results from oracle xmldb
Posted by Uma S Mudunuri <um...@yahoo.com>.
Thanks for the reply. Guess I have to go with the JDBC
implementation atleast for now.
--- Jan Vissers <Ja...@cumquat.nl> wrote:
> I'm guessing this has to do with the fact that
> iBatis doesn't support
> the Oracle XMLType datatype. I've also been looking
> for a way to deal
> with this, but more in the area of Custom Type
> Handling. Would be
> interested if someone has actually managed to do
> this. In my situation,
> due to time constraints we decided to implement
> XMLType operations as
> JDBC implemenation behind the iBatis DAO framework
> and do the rest with
> sqlMap implemantions.
>
> Uma S Mudunuri wrote:
>
> >Hi,
> >
> >I am trying to use iBATIS to retrieve results from
> >Oracle's XML DB database(which stores whole xml
> >documents). It works fine when the results are the
> >text value of an element but when I try to use
> >functions to get whole or part of the xml document
> it
> >does not return any results. The query works fine
> when
> >executed directly in oracle. I am new to iBATIS and
> >have searched the archives and faq's but did not
> find
> >anything related.
> >
> >An example xml file in the database
> >
> ><employee>
> ><name>Scott</name>
> ><department>1</department>
> ></employee>
> >
> >i) query to get name of the employee
> > <statement ....>
> > SELECT extractValue(value(x),'/employee/name')
> name
> > FROM EMPLOYEES
> > </statement>
> > This query works fine and the result is 'Scott'
> >
> >ii) query to get the whole xml document
> > <statement ....>
> > SELECT extract(value(x),'/employee') xml
> > FROM employees
> > </statement>
> >
> > does not return anything
> >
> >
> >I am guessing that the problem is because of the
> fact
> >that the result is an xml document and I would like
> to
> >know if there's any way to handle these queries in
> >iBATIS. Any help would be greatly appreciated.
> >
> >Thanks,
> >Uma
> >
> >
> >
>
>____________________________________________________
> >Start your day with Yahoo! - make it your home page
>
> >http://www.yahoo.com/r/hs
> >
> >
> >
> >
>
> --
> Cumquat Information Technology
> De Dreef 19
> 3706 BR Zeist
> T +31 (0)30 - 6940490
> F +31 (0)10 - 6940499
> http://www.cumquat.nl
>
> Jan.Vissers@cumquat.nl
> M +31 6 5 11 169 556
>
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: problem retrieving xml results from oracle xmldb
Posted by Jan Vissers <Ja...@cumquat.nl>.
I'm guessing this has to do with the fact that iBatis doesn't support
the Oracle XMLType datatype. I've also been looking for a way to deal
with this, but more in the area of Custom Type Handling. Would be
interested if someone has actually managed to do this. In my situation,
due to time constraints we decided to implement XMLType operations as
JDBC implemenation behind the iBatis DAO framework and do the rest with
sqlMap implemantions.
Uma S Mudunuri wrote:
>Hi,
>
>I am trying to use iBATIS to retrieve results from
>Oracle's XML DB database(which stores whole xml
>documents). It works fine when the results are the
>text value of an element but when I try to use
>functions to get whole or part of the xml document it
>does not return any results. The query works fine when
>executed directly in oracle. I am new to iBATIS and
>have searched the archives and faq's but did not find
>anything related.
>
>An example xml file in the database
>
><employee>
><name>Scott</name>
><department>1</department>
></employee>
>
>i) query to get name of the employee
> <statement ....>
> SELECT extractValue(value(x),'/employee/name') name
> FROM EMPLOYEES
> </statement>
> This query works fine and the result is 'Scott'
>
>ii) query to get the whole xml document
> <statement ....>
> SELECT extract(value(x),'/employee') xml
> FROM employees
> </statement>
>
> does not return anything
>
>
>I am guessing that the problem is because of the fact
>that the result is an xml document and I would like to
>know if there's any way to handle these queries in
>iBATIS. Any help would be greatly appreciated.
>
>Thanks,
>Uma
>
>
>
>____________________________________________________
>Start your day with Yahoo! - make it your home page
>http://www.yahoo.com/r/hs
>
>
>
>
--
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)10 - 6940499
http://www.cumquat.nl
Jan.Vissers@cumquat.nl
M +31 6 5 11 169 556