Skip to Main Content
  • Questions

  • print an XMLTYPE Record Type variable

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, shyam.

Asked: February 13, 2019 - 5:22 pm UTC

Last updated: June 07, 2022 - 3:11 am UTC

Version: oracle 12c

Viewed 10K+ times! This question is

You Asked

we have a following definition
TYPE response_t IS RECORD (
doc XMLTYPE
);

A local variable is defined with that record type
lv_resp response_t;

I want to print the value this local variable has stored.I tried dbms_output but dint work.Can you please suggest me an alternative.

and Connor said...

SQL> set serverout on
SQL> declare
  2    x xmltype;
  3  begin
  4    x := xmltype('<xml><name>Connor</name></xml>');
  5
  6    dbms_output.put_line(x.getstringval());
  7  end;
  8  /
<xml><name>Connor</name></xml>

PL/SQL procedure successfully completed.


There's a getclobval as well for bigger ones

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Comment

Naresh, February 18, 2021 - 11:50 am UTC

The Answer is correct but I have the huge string so I used getclobval() which is not working. Giving error message "PL/SQL: numeric or value error".
Chris Saxon
February 22, 2021 - 4:44 pm UTC

Please could you show us a complete example of what you're doing?

dbms_output.put_line(x.getstringval()); consuming time

Akshat, June 04, 2022 - 5:52 pm UTC

x.getstringval()

for above member function of XML element,

is there an alternative of this function or any other way around to get the faster printing in string format using dbms_output.put_line.

I am using x.getstringval() in my project and it is consuming lot of time to print the data on the console.

Please help me to improve the performance of the code.

Connor McDonald
June 07, 2022 - 3:11 am UTC

Can you give us a full example of what you're doing with some timings to show us the issue
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.