Query lobby structure - generate an overview list of lobbies

COMPOSITE_PAGE has row-level security. You may not have permission to view every record or any at all.

This query will help you extract some of the information buried in the XML-encoded columns.

SELECT cp.id,
       -- XMLTYPE(value),
       EXTRACTVALUE(XMLTYPE(cp.value), 'Page/PageTitle') AS page_title,
       (SELECT LISTAGG(
                  EXTRACTVALUE(column_value , '/Element/@id'), ';'
               ) WITHIN GROUP (ORDER BY 1)
          FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(cp.value), 'Page/Layout/Groups/Group/Elements/Element')))) AS elements,
       (SELECT LISTAGG(
                  EXTRACTVALUE(column_value , '/DataSource/@id'), ';'
               ) WITHIN GROUP (ORDER BY 1)
          FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(cp.value), 'Page/DataSources/DataSource')))) AS data_sources
  FROM composite_page cp
 WHERE cp.value IS NOT NULL;