Generate XML Schema from oracle tables

CREATE OR REPLACE FUNCTION GEN_XML_SCHEMA(target_table varchar2) RETURN xmltype
AS
  xmlSchema XMLTYPE;
  currUser VARCHAR2(128);
BEGIN
  SELECT sys_context('USERENV','CURRENT_USER') 
  INTO currUser
  FROM dual;
 
  SELECT
    -- Begin schema
    xmlElement(
      "xsd:schema",
      xmlAttributes(
        'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsd",
        'http://xmlns.oracle.com/xdb' AS "xmlns:xdb",
        'http://' || target_table || '/xsd' AS "targetNamespace",
        'http://' || target_table || '/xsd' AS "xmlns:tns",
        'qualified' AS "elementFormDefault"
      ),
      -- Base type
      xmlElement(
        "xsd:complexType",
        xmlAttributes(
          currUser || '.Base_T' AS "name"
        )
      ),
      -- Element
      xmlElement(
        "xsd:element",
        xmlAttributes(
          currUser || '.' || target_table || 'S' AS "name",
          'tns:' || currUser || '.' || target_table || 'S_T' AS "type"
        )
      ),
      -- Rows type to hold multiple rows
      xmlElement(
        "xsd:complexType",
        xmlAttributes (
          currUser || '.' || target_table || 'S_T' AS "name"
        ),
        -- Multipe rows
        xmlElement (
          "xsd:sequence",
          xmlElement (
            "xsd:element",
            xmlAttributes (
              currUser || '.' || target_table AS "name",
              'tns:' || currUser || '.' || target_table || '_T' AS "type",
              'unbounded' AS "maxOccurs"
            )
          )
        )
      ),
      -- Row type to hold single row
      xmlElement (
        "xsd:complexType",
        xmlAttributes (
          currUser || '.' || target_table || '_T' AS "name"
        ),
        xmlElement(
            "xsd:complexContent",
            xmlElement(
                "xsd:extension",
                xmlAttributes (
                  'tns:' || currUser || '.Base_T' AS "base"
                ),
                -- Individual columns
                xmlElement (
                  "xsd:sequence",
                  (
                    xmlAgg(ELEMENT)
                  )
                )
            )
        )
      )
    )
  INTO xmlSchema
  FROM (
    SELECT INTERNAL_COLUMN_ID,
      CASE
        WHEN DATA_TYPE IN ('VARCHAR2', 'CHAR') THEN
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            ),
            xmlElement (
              "xsd:simpleType",
              xmlElement (
                "xsd:restriction",
                xmlAttributes (
                  'xsd:string' AS "base"
                ),
                xmlElement (
                  "xsd:maxLength",
                  xmlAttributes (
                  DATA_LENGTH AS "value"
                  )
                )
              )
            )
          )
        WHEN DATA_TYPE = 'DATE' THEN
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              --'xsd:dateTime' as "type",
              'xsd:date' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        WHEN DATA_TYPE = 'NUMBER' THEN
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(DATA_SCALE, 0, 'xsd:integer', 'xsd:double') AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        ELSE
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              'xsd:anySimpleType' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
      END ELEMENT
    FROM user_tab_cols c
    WHERE TABLE_NAME = target_table
    ORDER BY internal_column_id
  );
 
  RETURN xmlSchema;
END;
/

-- Complete schema
SELECT gen_xml_schema('MY_TABLE').extract('/*') FROM dual;
 
-- Select element and complexType
SELECT gen_xml_schema('RF_TAG_TXN').extract('/schema/element | /schema/complexType', 
'xmlns="http://www.w3.org/2001/XMLSchema"') FROM dual;

CREATE OR REPLACE DIRECTORY dir_temp AS 
    'C:\TEMP';
 
DECLARE
  TYPE tableNameArray IS varray(100) OF VARCHAR2(64);
  tableNames tableNameArray;
 
  outputFileName VARCHAR2(100) := 'MY_SCHEMA.xsd';
  outputFileHandler UTL_FILE.file_type;
 
  tableXmlType xmlType;
  xmlRootElementBegin VARCHAR2(500) := 
    '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xdb="http://xmlns.oracle.com/xdb"
    targetNamespace="http://my.db/xsd"
    xmlns:tns="http://my.db/xsd" 
    elementFormDefault="qualified">';
  xmlRootElementEnd VARCHAR2(500) := '</xsd:schema>';
BEGIN
  -- Database tables
  tableNames := tableNameArray(
    'TABLE1',
    'TABLE2');
 
  -- Open schema file to write
  outputFileHandler := UTL_FILE.fopen('DIR_TEMP', outputFileName, 'W');
  -- Write root element begin tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementBegin);
 
  -- Loop through all tables
  FOR tblName IN 1 .. tableNames.count loop
    DBMS_OUTPUT.put_line('Processing table: ' || tableNames(tblName));
 
    -- Generate xml schema from each table
    SELECT gen_xml_schema(tableNames(tblName)).extract(
        '/schema/element|/schema/complexType',
        'xmlns="http://www.w3.org/2001/XMLSchema"') 
    INTO tableXmlType
    FROM dual;
 
    -- Write xml schema
    IF tableXmlType IS NOT NULL THEN
      UTL_FILE.put_line(outputFileHandler, tableXmlType.getStringVal());
    END IF;
  END loop;
 
  -- Write root element end tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementEnd);
  -- Close file
  UTL_FILE.fclose(outputFileHandler);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/