Oracle SQL to XML

Question

I want to create a structure like this in oracle sql:

<Alpha>
  <Beta>
    <Omega>12345</Omega>
  </Betha>  
  <Beta>
    <Omega>67890</Omega>
  </Betha>  
</Alpha>

In my DB the repetitive structure is "Omega", but in XML i want to repeat "Beta" and have everyting inside "Alpha". How can i do that using the XML functions (like XMLForest) in sql?

Answer 1

-- Sample data
WITH your_table ( your_value ) AS (
  SELECT 12345 FROM DUAL UNION ALL
  SELECT 67890 FROM DUAL
)
-- Query
SELECT XMLElement(
         "Alpha",
         XMLAgg(
           XMLElement(
             "Beta",
             XMLElement(
               "Omega",
               your_value
             )
           )
         )
       )
FROM   your_table;