Hello,
Version 11.2.0.4 & 19.12
I am trying to understand why I am unable to use "INDENT SIZE=2" within XMLSERIALIZE function call in below scenario. Can someone please help?
Working SQL:
SQL> SELECT XMLSERIALIZE (
2 CONTENT XMLELEMENT (
3 "Owner", xmlattributes (owner AS "Name"),
4 XMLELEMENT (
5 "Type", xmlattributes (object_type AS "Object Type"),
6 XMLAGG (
7 XMLELEMENT ("Object", xmlattributes(object_name as "Name"))
8 )
9 )
10 )
11 -- INDENT SIZE=2
12 ) AS xmlserialize_doc
13 FROM (
14 SELECT owner, object_type, object_name, row_number() over (partition by owner, object_type order by object_name) rn
15 FROM dba_objects
16 WHERE owner = 'SYS'
17 and object_type = 'TABLE'
18 )
19 WHERE rn <= 30
20 group by owner, object_type;
XMLSERIALIZE_DOC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<Owner Name="SYS"><Type Object Type="TABLE"><Object Name="ACCESS$"></Object><Object Name="AQ$_AQ$_MEM_MC_L"></Object><Object Name="AQ$_AQ$_MEM_MC_I"></Object><Object Name="AQ$_AQ$_MEM_MC_H"></Object><Object Name="AQ$_AQ$_MEM_MC_G"></Object><Object Name="AQ$_ALERT_QT_T"></Object><Object Name="AQ$_ALE
RT_QT_S"></Object><Object Name="AQ$_ALERT_QT_L"></Object><Object Name="AQ$_ALERT_QT_I"></Object><Object Name="AQ$_ALERT_QT_H"></Object><Object Name="AQ$_ALERT_QT_G"></Object><Object Name="APPROLE$"></Object><Object Name="APPLY$_VIRTUAL_OBJ_CONS"></Object><Object Name="APPLY$_TABLE_STATS"></Object><O
bject Name="APPLY$_SOURCE_SCHEMA"></Object><Object Name="APPLY$_SOURCE_OBJ"></Object><Object Name="APPLY$_SERVER_STATS"></Object><Object Name="APPLY$_READER_STATS"></Object><Object Name="APPLY$_ERROR_TXN"></Object><Object Name="APPLY$_ERROR_HANDLER"></Object><Object Name="APPLY$_ERROR"></Object><Obj
ect Name="APPLY$_DEST_OBJ_OPS"></Object><Object Name="APPLY$_DEST_OBJ_CMAP"></Object><Object Name="APPLY$_DEST_OBJ"></Object><Object Name="APPLY$_COORDINATOR_STATS"></Object><Object Name="APPLY$_CONSTRAINT_COLUMNS"></Object><Object Name="APPLY$_CONF_HDLR_COLUMNS"></Object><Object Name="APPLY$_CHANGE
_HANDLERS"></Object><Object Name="APPLY$_BATCH_SQL_STATS"></Object><Object Name="ALERT_QT"></Object></Type></Owner>
Error when using INDENT:
SQL> SELECT XMLSERIALIZE (
2 CONTENT XMLELEMENT (
3 "Owner", xmlattributes (owner AS "Name"),
4 XMLELEMENT (
5 "Type", xmlattributes (object_type AS "Object Type"),
6 XMLAGG (
7 XMLELEMENT ("Object", xmlattributes(object_name as "Name"))
8 )
9 )
10 )
11 INDENT SIZE=2
12 ) AS xmlserialize_doc
13 FROM (
14 SELECT owner, object_type, object_name, row_number() over (partition by owner, object_type order by object_name) rn
15 FROM dba_objects
16 WHERE owner = 'SYS'
17 and object_type = 'TABLE'
18 )
19 WHERE rn <= 30
20 group by owner, object_type;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '=' instead of 'T'
Error at line 1
no rows selected
Thanks in advance