I want to generate XSD file of oracle table from stored procedure, I have created a function for same but it not shows proper table columns order.
create or replace
function GEN_XML_SCHEMA1(target_table varchar2) return xmltype
as
xmlSchema XMLTYPE;
begin
select
xmlElement(
"xsd:schema",
xmlAttributes(
'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd",
'http://xmlns.oracle.com/xdb' as "xmlns:xdb"
),
xmlElement(
"xsd:element",
xmlAttributes(
'ROWSET' as "name",
'rowset' as "type"
)
),
xmlElement(
"xsd:complexType",
xmlAttributes
(
'rowset' as "name"
),
xmlElement
(
"xsd:sequence",
xmlElement
(
"xsd:element",
xmlAttributes
(
'ROW' as "name",
table_name || '_T' as "type",
'unbounded' as "maxOccurs"
)
)
)
),
xmlElement
(
"xsd:complexType",
xmlAttributes
(
table_name || '_T' as "name"
),
xmlElement
(
"xsd:sequence",
(
xmlAgg(ELEMENT)
)
)
)
)
into xmlSchema
from (
select TABLE_NAME, 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
)
group by TABLE_NAME;
return xmlSchema;
end;
I have created above function but it gives not proper schema
example -- desc AGNCY Name Null Type
AGENCYID NOT NULL CHAR(4 CHAR)
NAME CHAR(60 CHAR)
MARKDELETED VARCHAR2(1)
and when I ran function its gives result
here tables column order has changed. I have checked many tools or other solution to generate XSD files but i need to generate from stored procedure.
I have Created a store procedure also but it shows error when table has more then 40 columns Please check store procedure also
create or replace
PROCEDURE Export_In_XXS1D
(
V_TABLE_NAME1 IN varchar2,
v_FLAG OUT NUMBER
)
AS
BEGIN
declare
filehandle1 utl_file.file_type;
v_FILENAME varchar2(50);
V_TABLE_NAME varchar2(50);
result clob;
BEGIN
V_TABLE_NAME :=trim(UPPER(V_TABLE_NAME1));
filehandle1 := utl_file.fopen ('MYXML', V_TABLE_NAME ||'.xsd', 'w');
select xmlElement
(
"xsd:schema",
xmlAttributes
(
'http://www.w3.org/2001/XMLSchema' as "xmlns:xs",
'unqualified' as "attributeFormDefault",
'qualified' as "elementFormDefault"
),
xmlElement
(
"xsd:element",
xmlAttributes
(
'ROWSET' as "name",
'rowset' as "type"
)
),
xmlElement
(
"xsd:complexType",
xmlAttributes
(
'rowset' as "name"
),
xmlElement
(
"xsd:sequence",
xmlElement
(
"xsd:element",
xmlAttributes
(
'ROW' as "name",
table_name as "type",
'unbounded' as "maxOccurs"
)
)
)
),
xmlElement
(
"xsd:complexType",
xmlAttributes
(
table_name as "type"
),
xmlElement
(
"xsd:sequence",
(
xmlAgg(ELEMENT)
)
)
)
).getStringVal() AS result
into result
from (select TABLE_NAME,COLUMN_ID,
case
when DATA_TYPE = 'VARCHAR2' then
xmlElement
(
"xsd:element",
xmlattributes
(
column_name as "name",
column_name as "xsd:SQLName",
DATA_TYPE as "xsd: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",
'xs:dateTime' as "type",
column_name as "xsd:SQLName",
DATA_TYPE as "xsd:SQLTYPE"
)
)
when DATA_TYPE = 'NUMBER' then
xmlElement
(
"xsd:element",
xmlattributes
(
column_name as "name",
'xs:integer' as "type",
column_name as "xsd:SQLName",
DATA_TYPE as "xsd:SQLTYPE"
)
)
else
xmlElement
(
"xsd:element",
xmlattributes
(
column_name as "name",
'xs:anySimpleType' as "type",
column_name as "xsd:SQLName",
DATA_TYPE as "xsd:SQLTYPE"
)
)
end ELEMENT
from user_tab_cols c
where c.TABLE_NAME = V_TABLE_NAME and c.column_id is not null
order by c.column_id
)
group by TABLE_NAME ;
dbms_xslprocessor.clob2file( result, 'MYXML', ''||V_TABLE_NAME||'.xsd');
v_FLAG := 1;
UTL_FILE.FCLOSE(filehandle1);
utl_file.fclose(filehandle1);
/* EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_FILE.FCLOSE(filehandle1);
v_FLAG := 0; */
END;
end Export_In_XXS1D;
and error was
Connecting to the database D1.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 169
ORA-06512: at "CTIPS.EXPORT_IN_XXS1D", line 18
ORA-06512: at line 7
Process exited.
Disconnecting from the database D1.
Please help me how can I create xsd files of tables from script?
