Paran0a Paran0a - 6 months ago 8
SQL Question

Counting rows when generating XML data

I'm generating a XML. I have a requirement to create some kind of sequence. Something like rownum or row_number().

select XMLROOT ( xmlagg(
xmlelement(
"table_description",
xmlelement(
"table_name",
max(table_name)
),
xmlelement(
"table_sequence"
),
xmlelement(
"table_columns",
xmlagg(
xmlforest(
column_name as "column_name",
'' as "column_sequence"
)
)
)
)
), VERSION '1.0', STANDALONE YES)
from all_tab_columns
where owner='SYS'
and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS')
group by table_name;


So for example in this query I would like to add one sequence for each table_name element that will increment by one , and one sequence for each iteration of column_name that will increment by one BUT will be reset on the next grouping.

Pseudo code

<table_description>
<table_sequence>1</table_sequence>
<table_name>xyz</table_name>
<table_columns>
<column_sequence>1</column_sequence>
<column_name>Column One</column_name>
<column_sequence>2</column_sequence>
<column_name>Column two</column_name>
</table_columns>
<table_description>
<table_sequence>2</table_sequence>
<table_name>abs</table_name>
<table_columns>
<column_sequence>1</column_sequence>
<column_name>Column One</column_name>
<column_sequence>2</column_sequence>
<column_name>Column two</column_name>
</table_columns>
<table_description>

Answer

You can change your from all_tab_columns to an inline view which generates the sequence numbers via dense_rank():

from (
  select atc.*,
    dense_rank() over (order by table_name) as table_sequence,
    dense_rank() over (partition by table_name order by column_name) as column_sequence
  from all_tab_columns atc
  where owner='SYS' 
  and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS') 
)

And you can then use those generated columns in your XML:

select XMLROOT ( xmlagg(
                   xmlelement(
                     "table_description", 
                      xmlelement(
                        "table_name",
                        table_name
                      ),
                      xmlelement(
                        "table_sequence",
                        table_sequence
                      ),
                      xmlelement(
                        "table_columns",
                        xmlagg(
                          xmlforest(
                          column_name as "column_name",
                          column_sequence as "column_sequence"
                          )
                        )
                      )
                  )
            ), VERSION '1.0', STANDALONE YES) 
from (
  select atc.*,
    dense_rank() over (order by table_name) as table_sequence,
    dense_rank() over (partition by table_name order by column_name) as column_sequence
  from all_tab_columns atc
  where owner='SYS' 
  and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS') 
)
where owner='SYS' 
and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS') 
group by table_name, table_sequence;

which gets:

<?xml version="1.0" standalone="yes"?>
<table_description>
  <table_name>ALL_APPLY_ENQUEUE</table_name>
  <table_sequence>1</table_sequence>
  <table_columns>
    <column_name>DESTINATION_QUEUE_NAME</column_name>
    <column_sequence>1</column_sequence>
    <column_name>RULE_OWNER</column_name>
    <column_sequence>3</column_sequence>
    <column_name>RULE_NAME</column_name>
    <column_sequence>2</column_sequence>
  </table_columns>
</table_description>
<table_description>
  <table_name>ALL_APPLY_EXECUTE</table_name>
  <table_sequence>2</table_sequence>
  <table_columns>
    <column_name>EXECUTE_EVENT</column_name>
    <column_sequence>1</column_sequence>
    <column_name>RULE_OWNER</column_name>
    <column_sequence>3</column_sequence>
    <column_name>RULE_NAME</column_name>
    <column_sequence>2</column_sequence>
  </table_columns>
</table_description>
<table_description>
  <table_name>ALL_APPLY_KEY_COLUMNS</table_name>
  <table_sequence>3</table_sequence>
  <table_columns>
    <column_name>APPLY_DATABASE_LINK</column_name>
    <column_sequence>1</column_sequence>
    <column_name>OBJECT_OWNER</column_name>
    <column_sequence>4</column_sequence>
    <column_name>OBJECT_NAME</column_name>
    <column_sequence>3</column_sequence>
    <column_name>COLUMN_NAME</column_name>
    <column_sequence>2</column_sequence>
  </table_columns>
</table_description>

Your generated XML is still not right though, as there is no link between each column and its sequence number. Maybe you want something more like:

select XMLROOT ( xmlagg(
                   xmlelement(
                     "table_description", 
                      xmlelement(
                        "table_name",
                        table_name
                      ),
                      xmlelement(
                        "table_sequence",
                        table_sequence
                      ),
                      xmlelement(
                        "table_columns",
                        xmlagg(
                          xmlelement(
                            "column_description", 
                            xmlforest(
                              column_name as "column_name",
                              column_sequence as "column_sequence"
                            )
                          )
                        )
                      )
                  )
            ), VERSION '1.0', STANDALONE YES) 
from (
  select atc.*,
    dense_rank() over (order by table_name) as table_sequence,
    dense_rank() over (partition by table_name order by column_name) as column_sequence
  from all_tab_columns atc
  where owner='SYS' 
  and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS') 
)
where owner='SYS' 
and table_name in ('ALL_APPLY_ENQUEUE','ALL_APPLY_EXECUTE','ALL_APPLY_KEY_COLUMNS') 
group by table_name, table_sequence;

to get:

<?xml version="1.0" standalone="yes"?>
<table_description>
  <table_name>ALL_APPLY_ENQUEUE</table_name>
  <table_sequence>1</table_sequence>
  <table_columns>
    <column_description>
      <column_name>DESTINATION_QUEUE_NAME</column_name>
      <column_sequence>1</column_sequence>
    </column_description>
    <column_description>
      <column_name>RULE_OWNER</column_name>
      <column_sequence>3</column_sequence>
    </column_description>
    <column_description>
      <column_name>RULE_NAME</column_name>
      <column_sequence>2</column_sequence>
    </column_description>
  </table_columns>
</table_description>
<table_description>
  <table_name>ALL_APPLY_EXECUTE</table_name>
  <table_sequence>2</table_sequence>
  <table_columns>
    <column_description>
      <column_name>EXECUTE_EVENT</column_name>
      <column_sequence>1</column_sequence>
    </column_description>
    <column_description>
      <column_name>RULE_OWNER</column_name>
      <column_sequence>3</column_sequence>
    </column_description>
    <column_description>
      <column_name>RULE_NAME</column_name>
      <column_sequence>2</column_sequence>
    </column_description>
  </table_columns>
</table_description>
<table_description>
  <table_name>ALL_APPLY_KEY_COLUMNS</table_name>
  <table_sequence>3</table_sequence>
  <table_columns>
    <column_description>
      <column_name>APPLY_DATABASE_LINK</column_name>
      <column_sequence>1</column_sequence>
    </column_description>
    <column_description>
      <column_name>OBJECT_OWNER</column_name>
      <column_sequence>4</column_sequence>
    </column_description>
    <column_description>
      <column_name>OBJECT_NAME</column_name>
      <column_sequence>3</column_sequence>
    </column_description>
    <column_description>
      <column_name>COLUMN_NAME</column_name>
      <column_sequence>2</column_sequence>
    </column_description>
  </table_columns>
</table_description>