How to get the size of an XMLTYPE column in a row

[j@vaguy]

I've got a table, my_table with a column of XMLTYPE called xmldoc. This table has thousands of records. I would like to get the size of the xmldoc column for each row. I suppose I could open the value in the TOAD editor, copy the XML, past it in notepad and check the size...is there an easier way?

This didn't seem to do it:

select avg(vsize(XMLDOC)) from my_table


I appreciate any help. Thanks.

[jay_l_a]

Will

select length(XMLDOC) from my_table

not do it?

[j@vaguy]

Is that the length of characters in the XML? How does that translate to bytes, or KB?

thanks

[jay_l_a]

I apologize,
You need to pull out the spaces and the CR's, as it depends how the xml was formatted.

SELECT LENGTH(REPLACE(REPLACE(XMLDOC, CHR(10), NULL), CHR(32), NULL))
FROM my_table


Should give you the size in bytes...
J

[j@vaguy]

Ok I see. Yes that looks like it will work. Thanks.

[gunalmel]

Here is the one I am using:

select dbms_lob.getlength(xmltype.getclobval(xmldoc)) from my_table

Hope it helps posting this after almost a year but I am a new user looking for the same answer.