How to get the size of an XMLTYPE column in a row
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.
select length(XMLDOC) from my_table
not do it?
Is that the length of characters in the XML? How does that translate to bytes, or KB?
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))
Should give you the size in bytes...
Ok I see. Yes that looks like it will work. Thanks.
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.
Click Here to Expand Forum to Full Width