XDB XLMSCHEMA

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Thu, 06 Nov 2003 06:29:28 -0800
Message-ID: <F001.005D5D52.20031106062928@fatcity.com>

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: M.Godlewski
  INET: mcgodlewski_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Nov 06 2003 - 08:29:28 CST