# Oracle XMLType data

**URL:** <https://www.ruby-forum.com/t/oracle-xmltype-data/54484>\
**Category:** Rails\
**Created:** [March 6, 2006, 8:29pm UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484 "2006-03-06T20:29:41Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 6, 2006, 8:29pm UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/1 "2006-03-06T20:29:41Z")

</div>

Howdy folks,

Has anyone had any luck getting Oracle’s XMLType to play nicely with  
Rails? According to the docs, it’s just a wrapper around CLOB.  
However the ruby-oci8 driver throws an exception about user defined  
types if you try to query a table with an XMLType column.

Any pointers would be appreciated.

Thanks,

-wilig

---

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 7, 2006, 12:04am UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/2 "2006-03-07T00:04:17Z")

</div>

I haven’t played with it but it would be cool to get it back as a REXML  
document.

Tony

---

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 7, 2006, 12:16am UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/3 "2006-03-07T00:16:27Z")

</div>

Tony C. wrote:

> I haven’t played with it but it would be cool to get it back as a REXML  
> document.

Yes, that would be cool. 🙂

> ```
> types if you try to query a table with an XMLType column.
> 
> ```

I suspect it’s because the XMLType datatype was added in Oracle 9i. I’m  
not  
sure if Kubo would be willing to support this or if we need to wait for  
ruby-oci9. 🙂

I recommend posting a feature request on the ruby-oci8 project page.

Regards,

Dan

---

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 9, 2006, 5:57am UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/4 "2006-03-09T05:57:05Z")

</div>

Kubo,

Thanks so much for your guidance. Works great. I also found the if  
you TRIM the column during the select the clob value will be returned  
as a string. I’m not sure if this will fail if the string is over  
4096 characters though. Looking thru the 9i docs they claim to have  
removed this limit.

Well either way, I now have a working solution. I will try my hand at  
writing a plugin for Rails that allows you to retrieve the value as a  
REXML document.

I’m thinking of something along these lines

class Product \< ActiveRecord::Base  
as\_xml :description  
end

Need it for a project I’m working on, and it will be a good way to get  
to know ActiveRecord.

-wilig

---

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 7, 2006, 2:15pm UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/5 "2006-03-07T14:15:19Z")

</div>

Hi,

## XMLTYPE can be retrieved as CLOB as following:

require ‘oci8’

def select\_as\_csv(conn, sqltext)  
conn.exec(sqltext) do |row|  
row.collect! do |col|  
case col  
when OCI8::LOB  
col.read()  
else  
col  
end  
end  
puts(row.join(‘,’))  
end  
end

conn = OCI8.new(‘ruby’, ‘oci8’)

#conn.exec(“CREATE TABLE po\_tab OF XMLTYPE”)  
#conn.exec(“CREATE TABLE po\_xml\_tab(poid NUMBER(10), poDoc XMLTYPE)”)

xml1 = ‘JohnSmith’  
xml2 = ‘bar’

conn.exec(‘insert into po\_tab values(:1)’, xml1)  
conn.exec(‘insert into po\_tab values(:1)’, xml2)  
select\_as\_csv(conn, ‘select XMLTYPE.getClobVal(value(x)) from po\_tab x’)

conn.exec(‘insert into po\_xml\_tab values(:1, :2)’, 1, xml1);  
conn.exec(‘insert into po\_xml\_tab values(:1, :2)’, 2, xml2);  
select\_as\_csv(conn, ‘select poid, XMLTYPE.getClobVal(podoc) from  
po\_xml\_tab’)

## conn.rollback

To insert a XML value, bind as a String.  
To select a XML value, use XMLType.getClobVal(CLOBColumn).

I don’t know how to use it with Rails. :-\<  
I had not successed to use Rails with Oracle until last week, when  
“Agile Web Develepment with Rails” Japanese edition was published.

> > I haven’t played with it but it would be cool to get it back as a REXML  
> > document.
> 
> Yes, that would be cool. 🙂

Me too.

> I suspect it’s because the XMLType datatype was added in Oracle 9i. I’m not  
> sure if Kubo would be willing to support this or if we need to wait for  
> ruby-oci9. 🙂

User defined types, which are added in Oracle 8, have not been  
supported yet. :-p

I have a will to support XML Type. But ruby-oci8 is progressing in a  
very slow tempo. It won’t be supported before summer.

–  
KUBO Takehiro  
email: removed\_email\_address@domain.invalid  
web: [http://www.jiubao.org](http://www.jiubao.org)  
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262

---

<div class="post-metadata">

**Author:** ![wgamin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/9de0a6/32.png) [@wgamin](https://www.ruby-forum.com/u/wgamin)\
**Post date:** [March 11, 2006, 12:51am UTC](https://www.ruby-forum.com/t/oracle-xmltype-data/54484/6 "2006-03-11T00:51:03Z")

</div>

Patch submitted for adding Oracle XMLTYPE column support to Rails.  
Hopefully it will be accepted, and the oracle\_adapter will stop  
failing when an XMLTYPE column is present.

Next step: the ‘as\_xml’ plugin.

-wilig