Oracle-L: RE: XDB XLMSCHEMA

From: Hitchman, Peter <peter.hitchman_at_derwent.co.uk>
Date: Thu, 06 Nov 2003 08:49:26 -0800
Message-ID: <F001.005D5D81.20031106084926@fatcity.com>

Hi,
I do not have a tool for what you want. But I did experiment with XDB during the Summer of 2002. There is a XML IDE called XML Spy that integrates with Oracle XDB ( http://www.altova.com/ <http://www.altova.com/> ) and I suggest that you go to the XDB forum on Technet to ask your questions, I do not think that XDB is widley enough used to get answers on this forum.

Regards

Pete

-----Original Message-----
Sent: 06 November 2003 14:29
To: Multiple recipients of list ORACLE-L

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"'
<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" <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 <http://antispam.yahoo.com/whatsnewfree> your identity with Yahoo! Mail AddressGuard

The information contained in this email is confidential and intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. Thomson Scientific will accept no responsibility or liability in respect to this email other than to the addressee. If you have received this communication in error, please notify us immediately via email: ITHelpdesk_at_derwent.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hitchman, Peter
  INET: peter.hitchman_at_derwent.co.uk

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 - 10:49:26 CST