Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Generate basic XML tag

ronald_2017Mar 22 2024

Hello All,

I use Oracle 19c, the following query gives the below output. I have 2 questions regarding the below data.

with
q1 as (
select 1 row_r, '1:5' row_spans, '15.6' row_ht, '' row_thicktop, '0.3' row_dydescent, 'A10' c_r, 38  c_s, 's' c_t,  20 c_v from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'B10', 39, 's', 14 from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'C10', 15, '', null from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'D10', 15, '', null from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'E10', 16, '', null from dual
)
select 
  xmlserialize(document
      xmlagg(
      xmlelement("row", xmlattributes(row_r as "r", row_spans as "spans", row_ht as "ht", row_dydescent as "x14ac:dyDescent"),
                 xmlagg(
                 xmlelement("c", xmlattributes(c_r as "r", c_s as "s"), xmlforest(c_v as "v")
                           )
                 order by c_r)
                )
      order by row_r)
  as clob
  )
from q1
group by row_r, row_spans, row_ht, row_thicktop, row_dydescent;
<row r="1" spans="1:5" ht="15.6" x14ac:dyDescent="0.3">
<c r="A10" s="38"><v>20</v></c>
<c r="B10" s="39"><v>14</v></c>
<c r="C10" s="15"></c>
<c r="D10" s="15"></c>
<c r="E10" s="16"></c>
</row>

However, I want the below output. In other words, I want C tag closed like <c ../> this if there is no V tag inside. So, my first question is how can I get the below data?

<row r="10" spans="1:5" ht="15.6" x14ac:dyDescent="0.3">
<c r="A10" s="38" t="s"><v>20</v></c>
<c r="B10" s="39" t="s"><v>14</v></c>
<c r="C10" s="15"/>
<c r="D10" s="15"/>
<c r="E10" s="16"/>
</row>

My second question is when I write INDENT keyword it gives ORA-31011 xml parsing failed error. What should I do in order to prevent this error?

with
q1 as (
select 1 row_r, '1:5' row_spans, '15.6' row_ht, '' row_thicktop, '0.3' row_dydescent, 'A10' c_r, 38  c_s, 's' c_t,  20 c_v from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'B10', 39, 's', 14 from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'C10', 15, '', null from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'D10', 15, '', null from dual
union all
select 1, '1:5', '15.6', '', '0.3', 'E10', 16, '', null from dual
)
select 
  xmlserialize(document
      xmlagg(
      xmlelement("row", xmlattributes(row_r as "r", row_spans as "spans", row_ht as "ht", row_dydescent as "x14ac:dyDescent"),
                 xmlagg(
                 xmlelement("c", xmlattributes(c_r as "r", c_s as "s"), xmlforest(c_v as "v")
                           )
                 order by c_r)
                )
      order by row_r)
  as clob
  indent
  )
from q1
group by row_r, row_spans, row_ht, row_thicktop, row_dydescent;

Thanks in advance

Comments
Post Details
Added on Mar 22 2024
2 comments
128 views