Create XML Schema on the FLY

OTN Thread to create XML on the FLY

https://forums.oracle.com/forums/thread.jspa?threadID=426163


CREATE OR REPLACE
PROCEDURE GEN_XML_SCHEMA_OUTPUT
(p_schema_name VARCHAR2, p_table_name VARCHAR2) AS
CURSOR c_table_columns(c_table_name varchar2, c_schema_name varchar2) IS
SELECT column_name, data_type, data_default
FROM all_tab_columns
WHERE table_name = c_table_name
AND owner = c_schema_name;
l_col_str VARCHAR2(2000);
BEGIN

DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('<'>');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
FOR l_cur_table_columns IN c_table_columns(p_table_name, p_schema_name) LOOP
l_col_str := 'ELSIF l_cur_table_columns.data_type = 'NUMBER' THEN
l_col_str := l_col_str || 'xs:integer' || '"';
ELSIF l_cur_table_columns.data_type = 'DATE' THEN
l_col_str := l_col_str || 'xs:date' || '"';
END IF;

IF l_cur_table_columns.data_default IS NOT NULL THEN
l_col_str := l_col_str || ' default="' || TRIM(l_cur_table_columns.data_default) || '"';
END IF;
l_col_str := l_col_str || '/>';
DBMS_OUTPUT.PUT_LINE( l_col_str);
END LOOP;
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
END;