Recently i was finding a way across generating XSD from Oracle Tables. I found a way as below which fulfills my requirement.
Create a plsql function in oracle as below, i used oracle 10G
create or replace function GENERATE_XSD(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;
Run it as below ::
select GENERATE_XSD('EMPLOYEE_SALARY_TABLE').extract('/*') from dual;
Dont forget to put '.extract('/*')' before from dual.
Cheers,
Ujjwal Soni
5 comments:
Hi...thanks for the post..I just need to do the opposite. I've a XML schema(.xsd) file and need to create Oracle tables from to match the XML schema. How can I do that?
Thanks
Send me ur email address, i can send you a document for that.
Hi, i have an xsd and an xml. can i associate the xsd with the xml So tht i can view the values in the xml arranged in columns. my mail id is anshisansh@gmail.com . Please mail me the procedure
I've a XML schema(.xsd) file and need to create Oracle tables from to match the XML schema. How can I do that?
OK.. I need exactly what this function i saying it produces, but when I run this function in SQL Developer, I get an error "ORA-22275: invalid LOB locator specified". This table does not have any LOBs, so I am guessing it is talking about the output. Any ideas?
Post a Comment