How to get the size of an XMLTYPE column in a row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

Hybrid View

  1. #1
    Join Date
    May 2004
    Location
    Central FL
    Posts
    12

    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.

  2. #2
    Join Date
    Sep 2007
    Posts
    36
    Will

    select length(XMLDOC) from my_table

    not do it?

  3. #3
    Join Date
    May 2004
    Location
    Central FL
    Posts
    12
    Is that the length of characters in the XML? How does that translate to bytes, or KB?

    thanks

  4. #4
    Join Date
    Sep 2007
    Posts
    36
    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

  5. #5
    Join Date
    May 2004
    Location
    Central FL
    Posts
    12
    Ok I see. Yes that looks like it will work. Thanks.

  6. #6
    Join Date
    Aug 2008
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width