The following query will generate a XML XSD for any database table. Useful when generating SOA Entity components
-- GENERATE XSD FROM DATABASE TABLE (11g)
-- THE PURPOSE OF THIS "WITH" prequery is to convert the default column value (DATA_DEFAULT) from a LONG to a STRING so that it can be used in the next query below
-- and added to the XSD Element attribute named "default"
-- This is done by converting to to XML and then back to a table
WITH sel AS (SELECT 'SELECT column_name ,DATA_DEFAULT FROM ALL_TAB_COLUMNS where table_name=''' || :tableName || '''' FROM dual ),
xml AS ( SELECT XMLTYPE( DBMS_XMLGEN.GETXML( (SELECT * FROM sel ))) AS xml FROM dual ),
defaultdata AS
(SELECT column_name, data_default
FROM
( SELECT extractValue(xs.object_value, '/ROW/COLUMN_NAME') AS column_name ,
REPLACE(extractValue(xs.object_value, '/ROW/DATA_DEFAULT'),'''','') AS DATA_DEFAULT
FROM xml x ,
TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
)
WHERE (DATA_DEFAULT IS NOT NULL
AND UPPER(DATA_DEFAULT) != 'NULL')
)
SELECT xmlElement( "xsd:schema", xmlAttributes( 'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsd", NVL(:targetNamespace,'http://set.this.namesspace') AS "targetNamespace" , 'qualified' AS "elementFormDefault", '1.0' AS "version" ) , xmlElement ( "xsd:annotation",xmlElement ( "xsd:documentation",
(SELECT comments FROM all_tab_comments atc WHERE atc.table_name = :tableName
) ) ), xmlElement ( "xsd:element", xmlAttributes ( REPLACE(initcap(REPLACE(table_name, '_', ' ')), ' ', '')
|| 'Element' AS "name", REPLACE(initcap(REPLACE(table_name, '_', ' ')), ' ', '')
|| 'Type' AS "type") ) , xmlElement ( "xsd:complexType", xmlAttributes ( REPLACE(initcap(REPLACE(table_name, '_', ' ')), ' ', '')
|| 'Type' AS "name" ), xmlElement ( "xsd:sequence", ( xmlAgg(ELEMENT
ORDER BY COLUMN_NAME) ) ) ) )
FROM
(SELECT c.TABLE_NAME,
c.COLUMN_NAME,
CASE
WHEN DATA_TYPE IN ('VARCHAR2', 'CHAR')
THEN xmlElement ( "xsd:element", xmlattributes ( REPLACE(initcap(REPLACE(c.COLUMN_NAME, '_', ' ')), ' ', '') AS "name", DECODE(NULLABLE, 'Y', 0, 1) AS "minOccurs", dd.DATA_DEFAULT AS "default" ), NVL2(c1.comments,xmlElement ( "xsd:annotation", xmlElement ( "xsd:documentation",c1.comments ) ),''), 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 ( REPLACE(initcap(REPLACE(c.COLUMN_NAME, '_', ' ')), ' ', '') AS "name",
--'xsd:dateTime' as "type",
'xsd:date' AS "type", DECODE(NULLABLE, 'Y', 0, 1) AS "minOccurs", dd.DATA_DEFAULT AS "default") , NVL2(c1.comments,xmlElement ( "xsd:annotation",xmlElement ( "xsd:documentation",c1.comments ) ),'') )
WHEN DATA_TYPE = 'NUMBER'
THEN xmlElement ( "xsd:element", xmlattributes ( REPLACE(initcap(REPLACE(c.COLUMN_NAME, '_', ' ')), ' ', '') AS "name", DECODE(DATA_SCALE, 0, 'xsd:long', 'xsd:double') AS "type", DECODE(NULLABLE, 'Y', 0, 1) AS "minOccurs", dd.DATA_DEFAULT AS "default"), NVL2(c1.comments,xmlElement ( "xsd:annotation",xmlElement ( "xsd:documentation",c1.comments ) ),'') )
ELSE xmlElement ( "xsd:element", xmlattributes ( REPLACE(initcap(REPLACE(c.COLUMN_NAME, '_', ' ')), ' ', '') AS "name", 'xsd:anySimpleType' AS "type", DECODE(NULLABLE, 'Y', 0, 1) AS "minOccurs", dd.DATA_DEFAULT AS "default"), NVL2(c1.comments,xmlElement ( "xsd:annotation",xmlElement ( "xsd:documentation",c1.comments ) ),'') )
END ELEMENT
FROM ALL_TAB_COLUMNS c
LEFT JOIN all_col_comments c1 ON (c1.TABLE_NAME = :tableName AND c1.column_name = c.COLUMN_NAME) -- This join is for retrieving the column description
LEFT JOIN defaultdata dd ON (dd.column_name = c.COLUMN_NAME) -- this join is for retrieving the columns default value
WHERE c.TABLE_NAME = :tableName
AND c.column_name NOT IN ('CREATE_USER','MOD_USER','CREATE_DATE','MOD_DATE')
ORDER BY COLUMN_NAME
)
GROUP BY TABLE_NAME;