Spooling Oracle XML data results in 1 line and missing data? I am trying to spool Oracle XML data to a file but the resulting file ends up with 1 line which is stops after a certain amount of characters so doesn't contain all of the data?

set colsep ,
set feedback off
set headsep off
set linesize 32767
set pagesize 0
set trimspool on

spool $FILE

select
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", orders.order_no),
          xmlelement("date", orders.date),
          xmlelement("orderItems",
            xmlagg(
              xmlelement("orderItem",
                xmlelement("position", order_items.item_position)
              )
            )
          )
        )
      )
    )
  )
  as xml
from
  ...
You're probably hitting the default &quot;long&quot; data type display limit of 80 characters:

SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.