XDB XLMSCHEMA

[M.Godlewski]

List,

 

There doesn't seem to be any cool tools to generate XMLSchemas for XDB that I could find.

 

Here is a simple sql script to pull a table out of the data dictionary and generate a basic XMLSCHEMA to register in Oracle XDB.

 

I'm trying to figure out what the conversion is for Oracle datatypes to XML data types.

 

I figure Oracle's varchar2 datatype converts to XML's string datatype. 

 

Is there a document that would have the conversion somewhere?

 

Secondly, I'm trying to add the generation to the xmlschema for ref (i.e. <xsd:element ref="billto" minoccurs="0"/>) for constraints.  I know there is a lot of knowledge on this list.  Was wondering if any one had a decode or snipet of code that they would be willing to give me to add to my simple program to pull constraints and turn them into ref statements, or better yet point me to a tool.

 

TIA

M.

 

 

 

column dum1 noprint
column dum2 noprint
column dum3 noprint
column dum4 noprint

# set termout off
# set feedback off
#set verify off
# set echo off
set pagesize 0
set heading off

spool xml.out

 

select  table_name dum2, 6 dum3, 1 dum4,
 'DECLARE'
 from user_tables
UNION
select  table_name dum2, 7 dum3, 1 dum4,
 lower(table_name)||'schema VARCHAR2(2000) := ''<xs:schema'
 from user_tables
UNION
select  table_name dum2, 8 dum3, 1 dum4,
 'xmlns:xs="http://www.w3.org/2001/XMLSchema"'
 from user_tables
UNION
select  table_name dum2, 9 dum3, 1 dum4,
 'xmlns:xdb="http://xmlns.oracle.com/xdb">'
 from user_tables
UNION
select  table_name dum2, 10 dum3, 1 dum4,
 '<xs:element name="'|| table_name||'">'
 from user_tables
UNION
select  table_name dum2, 11 dum3, 1 dum4,
 '<xs:complexType xdb:SQLType="XML_'|| table_name||'">'
 from user_tables
UNION
select  table_name dum2, 12 dum3, 1 dum4,
 '<xs:sequence>'
 from user_tables
UNION
select  a.table_name, 20, a.column_id,
        decode( a.column_id, 1, '','')||
        rpad('<xs:element name="'||a.column_name||'"',40)
                  ||decode(a.data_type,
                    'VARCHAR2', 'VARCHAR2 ('||a.data_length||')',
                    'CHAR', 'type="xs:string"',
                    'NCHAR', 'type="xs:string"',
                    'NVARCHAR2', 'type="xs:string"',
                    'CLOB', 'type="xs:clob"',
                    'BLOB', 'type="xs:blob"',
                    'NUMBER','type="xs:float"',
                    'DATE', 'type="xs:date"',
                    'ERROR!! '||a.data_type||' not handled!')
                  ||decode( a.nullable, 'Y',' ', ' nillable="false"/>' )
                  ||decode( a.nullable, 'N','', '/>')
  from user_tab_columns a, user_tables b
  where a.table_name = b.table_name
UNION
select  table_name dum2, 30 dum3, 1 dum4,
 '</xs:sequence>'
 from user_tables
UNION
select  table_name dum2, 40 dum3, 1 dum4,
 '</xs:complexType>'
 from user_tables
UNION
select  table_name dum2, 50 dum3, 1 dum4,
 '</xs:element>'
 from user_tables
UNION
select  table_name dum2, 60 dum3, 1 dum4,
 '</xs:schema>'';'
 from user_tables
UNION
select  table_name dum2, 70 dum3, 1 dum4,
 'BEGIN'
 from user_tables
UNION
select  table_name dum2, 80 dum3, 1 dum4,
 'DBMS_XMLSCHEMA.RegisterSchema(''http://otn.oracle.com/'||lower(table_name)||'.xsd'' ,'||lower(table_name)||'schema );'
 from user_tables
UNION
select  table_name dum2, 90 dum3, 1 dum4,
 'END;'
 from user_tables
UNION
select  table_name dum2, 95 dum3, 1 dum4,
 '/'
 from user_tables
order by 1, 2, 3
/

 

 

---

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard