1 2 Previous Next 17 Replies Latest reply: Sep 18, 2011 1:02 PM by kenmadsen RSS

    Generate XML Schema from oracle tables

    535093
      I am new to xml...We have a requirement to generate xml schemas for one or more oracle tables...Are there any tools availaible in oracle?.....I have tried xml spy which generates the schema but i want it like a batch process probably without user intervention...
        • 1. Re: Generate XML Schema from oracle tables
          229023
          You can generate one with your own script. I've the script which does the basic thing and then i modify that.

          CREATE OR REPLACE
          PROCEDURE GEN_XML_SCHEMA_OUTPUT

          (p_schema_name VARCHAR2, p_table_name VARCHAR2) AS
          CURSOR c_table_columns(c_table_name varchar2, c_schema_name varchar2) IS
          SELECT column_name, data_type, data_default
          FROM all_tab_columns
          WHERE table_name = c_table_name
          AND owner = c_schema_name;
          l_col_str VARCHAR2(2000);
          BEGIN

          DBMS_OUTPUT.PUT_LINE('<?xml version="1.0" encoding="UTF-8"?>');
          DBMS_OUTPUT.PUT_LINE('<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">');
          DBMS_OUTPUT.PUT_LINE('<' || lower(p_table_name) || '>');
          DBMS_OUTPUT.PUT_LINE('<xs:complexType>');
          DBMS_OUTPUT.PUT_LINE('<xs:sequence>');
          FOR l_cur_table_columns IN c_table_columns(p_table_name, p_schema_name) LOOP
          l_col_str := '<xs:element name= "' || l_cur_table_columns.column_name || '" type="' ;
          IF l_cur_table_columns.data_type = 'VARCHAR2' THEN
          l_col_str := l_col_str || 'xs:string' || '"';
          ELSIF l_cur_table_columns.data_type = 'NUMBER' THEN
          l_col_str := l_col_str || 'xs:integer' || '"';
          ELSIF l_cur_table_columns.data_type = 'DATE' THEN
          l_col_str := l_col_str || 'xs:date' || '"';
          END IF;

          IF l_cur_table_columns.data_default IS NOT NULL THEN
          l_col_str := l_col_str || ' default="' || TRIM(l_cur_table_columns.data_default) || '"';
          END IF;
          l_col_str := l_col_str || '/>';
          DBMS_OUTPUT.PUT_LINE( l_col_str);
          END LOOP;
          DBMS_OUTPUT.PUT_LINE( '</xs:sequence>');
          DBMS_OUTPUT.PUT_LINE( '</xs:complexType>');
          DBMS_OUTPUT.PUT_LINE( '</' || lower(p_table_name) || '>');
          END;
          • 2. Re: Generate XML Schema from oracle tables
            mdrake
            Here's an example of how I would approach this.. Note that the type mapping is nowhere near complete....
            SQL> desc EMP
             Name                                                                                      Null?    Type
             ----------------------------------------------------------------------------------------- -------- ----------------------------------
            --------------------
             EMPNO                                                                                     NOT NULL NUMBER(4)
             ENAME                                                                                              VARCHAR2(10)
             JOB                                                                                                VARCHAR2(9)
             MGR                                                                                                NUMBER(4)
             HIREDATE                                                                                           DATE
             SAL                                                                                                NUMBER(7,2)
             COMM                                                                                               NUMBER(7,2)
             DEPTNO                                                                                             NUMBER(2)
            
            SQL> create or replace function generateXMLSchema(target_table varchar2)
              2  return xmltype
              3  as
              4    xmlSchema XMLTYPE;
              5  begin
              6    select xmlElement
              7           (
              8             "xsd:schema",
              9             xmlAttributes
             10             (
             11               'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd",
             12               'http://xmlns.oracle.com/xdb' as "xmlns:xdb"
             13             ),
             14             xmlElement
             15             (
             16               "xsd:element",
             17               xmlAttributes
             18               (
             19                 'ROWSET' as "name",
             20                 'rowset' as "type"
             21               )
             22             ),
             23             xmlElement
             24             (
             25               "xsd:complexType",
             26               xmlAttributes
             27               (
             28                 'rowset' as "name"
             29               ),
             30               xmlElement
             31               (
             32                 "xsd:sequence",
             33                 xmlElement
             34                 (
             35                    "xsd:element",
             36                    xmlAttributes
             37                    (
             38                      'ROW' as "name",
             39                      table_name || '_T' as "type",
             40                      'unbounded' as "maxOccurs"
             41                    )
             42                  )
             43                )
             44             ),
             45             xmlElement
             46             (
             47               "xsd:complexType",
             48               xmlAttributes
             49               (
             50                 table_name || '_T' as "type"
             51               ),
             52               xmlElement
             53               (
             54                 "xsd:sequence",
             55                 (
             56                   xmlAgg(ELEMENT)
             57                 )
             58               )
             59             )
             60           )
             61      into xmlSchema
             62      from (select TABLE_NAME, INTERNAL_COLUMN_ID,
             63                   case
             64                     when DATA_TYPE = 'VARCHAR2' then
             65                       xmlElement
             66                       (
             67                         "xsd:element",
             68                         xmlattributes
             69                         (
             70                           column_name as "name",
             71                           column_name as "xsd:SQLName",
             72                           DATA_TYPE as "xsd:SQLTYPE"
             73                         ),
             74                         xmlElement
             75                         (
             76                           "xsd:simpleType",
             77                           xmlElement
             78                           (
             79                             "xsd:restriction",
             80                             xmlAttributes
             81                             (
             82                               'xsd:string' as "base"
             83                             ),
             84                             xmlElement
             85                             (
             86                               "xsd:maxLength",
             87                               xmlAttributes
             88                               (
             89                                 DATA_LENGTH  as "value"
             90                               )
             91                             )
             92                           )
             93                         )
             94                       )
             95                     when DATA_TYPE = 'DATE' then
             96                       xmlElement
             97                       (
             98                         "xsd:element",
             99                         xmlattributes
            100                         (
            101                           column_name as "name",
            102                           'xs:dateTime' as "type",
            103                           column_name as "xsd:SQLName",
            104                           DATA_TYPE as "xsd:SQLTYPE"
            105                         )
            106                       )
            107                     when DATA_TYPE = 'NUMBER' then
            108                       xmlElement
            109                       (
            110                         "xsd:element",
            111                         xmlattributes
            112                         (
            113                           column_name as "name",
            114                           'xs:integer' as "type",
            115                           column_name as "xsd:SQLName",
            116                           DATA_TYPE as "xsd:SQLTYPE"
            117                         )
            118                       )
            119                     else
            120                       xmlElement
            121                       (
            122                         "xsd:element",
            123                         xmlattributes
            124                         (
            125                           column_name as "name",
            126                           'xs:anySimpleType' as "type",
            127                           column_name as "xsd:SQLName",
            128                           DATA_TYPE as "xsd:SQLTYPE"
            129                         )
            130                       )
            131                   end ELEMENT
            132              from user_tab_cols c
            133             where c.TABLE_NAME = target_table
            134               order by c.internal_column_id
            135            )
            136      group by TABLE_NAME;
            137    return xmlSchema;
            138  end;
            139  /
            
            Function created.
            
            SQL> set long 100000
            SQL> set pages 0 lines 160
            SQL> --
            SQL> var TARGET_TABLE varchar2(36)
            SQL> --
            SQL> begin
              2    :TARGET_TABLE := 'EMP';
              3  end;
              4  /
            
            PL/SQL procedure successfully completed.
            
            SQL> select generateXMLSchema(:TARGET_TABLE).extract('/*')
              2    from dual
              3  /
            <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
              <xsd:element name="ROWSET" type="rowset"/>
              <xsd:complexType name="rowset">
                <xsd:sequence>
                  <xsd:element name="ROW" type="EMP_T" maxOccurs="unbounded"/>
                </xsd:sequence>
              </xsd:complexType>
              <xsd:complexType type="EMP_T">
                <xsd:sequence>
                  <xsd:element name="EMPNO" type="xs:integer" xsd:SQLName="EMPNO" xsd:SQLTYPE="NUMBER"/>
                  <xsd:element name="ENAME" xsd:SQLName="ENAME" xsd:SQLTYPE="VARCHAR2">
                    <xsd:simpleType>
                      <xsd:restriction base="xsd:string">
                        <xsd:maxLength value="10"/>
                      </xsd:restriction>
                    </xsd:simpleType>
                  </xsd:element>
                  <xsd:element name="JOB" xsd:SQLName="JOB" xsd:SQLTYPE="VARCHAR2">
                    <xsd:simpleType>
                      <xsd:restriction base="xsd:string">
                        <xsd:maxLength value="9"/>
                      </xsd:restriction>
                    </xsd:simpleType>
                  </xsd:element>
                  <xsd:element name="HIREDATE" type="xs:dateTime" xsd:SQLName="HIREDATE" xsd:SQLTYPE="DATE"/>
                  <xsd:element name="COMM" type="xs:integer" xsd:SQLName="COMM" xsd:SQLTYPE="NUMBER"/>
                  <xsd:element name="DEPTNO" type="xs:integer" xsd:SQLName="DEPTNO" xsd:SQLTYPE="NUMBER"/>
                  <xsd:element name="SAL" type="xs:integer" xsd:SQLName="SAL" xsd:SQLTYPE="NUMBER"/>
                  <xsd:element name="MGR" type="xs:integer" xsd:SQLName="MGR" xsd:SQLTYPE="NUMBER"/>
                </xsd:sequence>
              </xsd:complexType>
            </xsd:schema>
            
            
            SQL> quit
            • 3. Re: Generate XML Schema from oracle tables
              535093
              Thankx a lot....guys...
              • 4. Re: Generate XML Schema from oracle tables
                540932
                thanks for the code, it helped me a lot, however it does not create a valid schema file. Here is the fix:

                create or replace function generateXMLSchema(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 "name",
                'unbounded' as "maxOccurs"
                )
                )
                )
                ),
                xmlElement
                (
                "xsd:complexType",
                xmlAttributes
                (
                table_name || '_T' as "type"
                ),
                xmlElement
                (
                "xsd:sequence",
                (
                xmlAgg(ELEMENT)
                )
                )
                )
                )
                into xmlSchema
                from (select TABLE_NAME, INTERNAL_COLUMN_ID,
                case
                when DATA_TYPE = 'VARCHAR2' then
                xmlElement
                (
                "xsd:element",
                xmlattributes
                (
                column_name as "name",
                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",
                column_name as "xdb:SQLName",
                DATA_TYPE as "xdb:SQLTYPE"
                )
                )
                when DATA_TYPE = 'NUMBER' then
                xmlElement
                (
                "xsd:element",
                xmlattributes
                (
                column_name as "name",
                'xsd:integer' as "type",
                column_name as "xdb:SQLName",
                DATA_TYPE as "xdb:SQLTYPE"
                )
                )
                else
                xmlElement
                (
                "xsd:element",
                xmlattributes
                (
                column_name as "name",
                'xs:anySimpleType' as "type",
                column_name as "xdb:SQLName",
                DATA_TYPE as "xdb:SQLTYPE"
                )
                )
                end ELEMENT
                from user_tab_cols c
                where c.TABLE_NAME = target_table
                order by c.internal_column_id
                )
                group by TABLE_NAME;
                return xmlSchema;
                end;
                /

                and one additional note: don't try to execute it with a Oracle 8 client, creates only pain ;-) because it cannot deal with the XmlType.

                Christian Sy
                • 5. Re: Generate XML Schema from oracle tables
                  609702
                  I fixed 2 small bugs and add handling of minOccurs and char and double type
                  here is the proper code:


                  create or replace function GEN_XML_SCHEMA(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;
                  • 6. Re: Generate XML Schema from oracle tables
                    mdrake
                    Thanks for sharing the updates..
                    • 7. Re: Generate XML Schema from oracle tables
                      693872
                      I digged out this old thread...Created generateXMLSchema as exampled...but the select generateXMLSchema returns nothing.


                      SQL> select generateXMLSchema('msg_rqst').extract('/*') from dual;

                      GENERATEXMLSCHEMA('MSG_RQST').EXTRACT('/*')
                      --------------------------------------------------------------------------------

                      Here is the table

                      SQL> desc msg_rqst
                      Name Null? Type
                      ----------------------------------------- -------- ----------------------------
                      RQST_ID NOT NULL NUMBER(10)
                      MSG_SK NOT NULL NUMBER(10)
                      FIRM_CRD_NB NUMBER(8)
                      AS_OF_DT DATE
                      SCHM_VRSN_NB NUMBER(6)
                      RQST_TYPE_ID NUMBER(6)
                      MTTR_ID VARCHAR2(11)
                      DUE_DT DATE
                      CMPLT_FROM_DT DATE
                      CMPLT_TO_DT DATE
                      PBLSH_ON_DT DATE
                      RQST_PRCSD_DT DATE
                      CRRNT_STTS_ID NUMBER(6)
                      FIRM_NM VARCHAR2(500)
                      CMMNT_TX VARCHAR2(4000)

                      SQL>

                      Mike, please help.
                      Thanks!
                      • 8. Re: Generate XML Schema from oracle tables
                        407338
                        Hi there,
                        Please try the one that he fixed two bugs (generate_xml_schema). I copied it and tested it with a simple table and it works. The one you are using works if I don't extract('/*') it. If I do, I get these errors:

                        ERROR:
                        ORA-31011: XML parsing failed
                        ORA-19202: Error occurred in XML processing
                        LPX-00224: multiple occurrences of attribute "name" found
                        Error at line 1
                        ORA-06512: at "SYS.XMLTYPE", line 111

                        But, select GEN_XML_SCHEMA('RACE').extract('/*') from dual:

                        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                        <xsd:element name="ROWSET" type="rowset"/>
                        <xsd:complexType name="rowset">
                        <xsd:sequence>
                        <xsd:element name="ROW" type="RACE_T" maxOccurs="unbounded"/>
                        </xsd:sequence>
                        </xsd:complexType>
                        <xsd:complexType name="RACE_T">
                        <xsd:sequence>
                        <xsd:element name="RACE_CODE" minOccurs="1" xdb:SQLName="RACE_CODE" xdb:SQLTYPE="VARCHAR2">
                        <xsd:simpleType>
                        <xsd:restriction base="xsd:string">
                        <xsd:maxLength value="1"/>
                        </xsd:restriction>
                        </xsd:simpleType>
                        </xsd:element>
                        <xsd:element name="RACE_DESC" minOccurs="1" xdb:SQLName="RACE_DESC" xdb:SQLTYPE="VARCHAR2">
                        <xsd:simpleType>
                        <xsd:restriction base="xsd:string">
                        <xsd:maxLength value="50"/>
                        </xsd:restriction>
                        </xsd:simpleType>
                        </xsd:element>
                        </xsd:sequence>
                        </xsd:complexType>
                        </xsd:schema>

                        Thanks for sharing this! It's pretty cool.

                        Ben
                        • 9. Re: Generate XML Schema from oracle tables
                          693872
                          I did use the one which had 2 bugs fixed...i don't get error but it doesn't return anything.

                          I am using 10.2.0.3

                          Thanks!
                          • 10. Re: Generate XML Schema from oracle tables
                            407338
                            That is strange. I also use 10.2.0.3:
                            Connected to:
                            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
                            With the Partitioning, OLAP and Data Mining options

                            Maybe Mark will have some idea for you.

                            ben
                            • 11. Re: Generate XML Schema from oracle tables
                              Marco Gralike
                              Maybe too obvious...? Wild guess though, you used lower case for refering to your table............

                              Try UPPER case...

                              So
                              select generateXMLSchema('MSG_RQST').extract('/*') from dual;
                              instead off
                              select generateXMLSchema('msg_rqst').extract('/*') from dual;
                              As long as you don't use double quotes in Oracle and/or are not XMLDB related, objects recreated or refered are in UPPER case, by default. The moment you use double quotes like the following
                              create table "Emp" (data date);
                              objects become Case Sensitive.
                              • 12. Re: Generate XML Schema from oracle tables
                                693872
                                Marco,

                                You are so right!!

                                Now do you have some example code to generate xml instance document out of data from a relational table?
                                thanks!
                                • 13. Re: Generate XML Schema from oracle tables
                                  Marco Gralike
                                  There are multiple ways to generate XML from relational table content. The following gives you an impression how to achieve this via XMLelement, XMLAgg, XMLForest, etc.
                                  SELECT  department_id, 
                                          XMLElement("Department", 
                                                  XMLAttributes(d.department_name "name"), 
                                                  (SELECT XMLAgg( XMLElement("emp", 
                                                                  XMLAttributes(e.last_name name), 
                                                                  (SELECT XMLAgg( XMLElement("jobs", 
                                                                                  XMLAttributes(j.job_id "job"))) 
                                                                   FROM job_history j 
                                                                   WHERE j.employee_id=e.employee_id))) 
                                                  FROM   employees e 
                                                  WHERE e.department_id=d.department_id)) AS result 
                                  FROM    departments d 
                                  WHERE   department_id < 40; 
                                  Output
                                  Id                     Result 
                                  ----------      ----------------------------------------------------------------------
                                  90                     <Department name="Executive"> 
                                                           <emp NAME="King"/> 
                                                           <emp NAME="Kochhar"> 
                                                                <jobs job="AC_ACCOUNT"/> 
                                                                <jobs job="AC_MGR"/> 
                                                           </emp> 
                                                           <emp NAME="De Haan"> 
                                                                <jobs job="IT_PROG"/> 
                                                           </emp> 
                                                      </Department>
                                  
                                  …rows selected.
                                  • 14. Re: Generate XML Schema from oracle tables
                                    Keith Hollins
                                    I took this and used it to make a routine to export, via a text XML file on UNIX, data from a 10g Oracle table which could be parsed by .NET framework 3.5 (I think) and loaded up on SQL Server 2000 / 2005. By way of feeding back to the forum something, this is what I ended up with (commented out bits were bits that had to be commented out to make it work but that I was switching in and out with in my attempts):

                                    FUNCTION gen_xml_schema (p_target_table VARCHAR2) RETURN XMLTYPE
                                    IS
                                    l_xmlSchema XMLTYPE;
                                    BEGIN
                                    SELECT XMLELEMENT
                                    ( "xs:schema"
                                    , XMLATTRIBUTES
                                    ( 'ROWSET' AS "id"
                                    , 'http://www.w3.org/2001/XMLSchema' AS "xmlns:xs"
                                    , 'http://xmlns.oracle.com/xdb' AS "xmlns:xdb"
                                    , 'urn:schemas-microsoft-com:xml-msdata' AS "xmlns:msdata"
                                    )
                                    , XMLELEMENT
                                    ( "xs:complexType"
                                    , XMLATTRIBUTES (table_name||'_T' AS "name")
                                    , XMLELEMENT
                                    ( "xs:sequence"
                                    , (XMLAGG (column_xml))
                                    )
                                    )
                                    , XMLELEMENT
                                    ( "xs:element"
                                    , XMLATTRIBUTES ('ROWSET' AS "name"
                                    -- ,'rowset' AS "type"
                                    ,'true' AS "msdata:IsDataSet"
                                    ,'en-US' AS "msdata:Locale"
                                    )
                                    , XMLELEMENT
                                    ( "xs:complexType"
                                    -- , XMLATTRIBUTES ('rowset' AS "name"
                                    -- )
                                    , XMLELEMENT
                                    ( "xs:choice"
                                    , XMLATTRIBUTES
                                    ( '0' AS "minOccurs"
                                    , 'unbounded' AS "maxOccurs"
                                    )
                                    , XMLELEMENT
                                    ( "xs:element"
                                    , XMLATTRIBUTES
                                    ('ROW' AS "name"
                                    , table_name||'_T' AS "type"
                                    , 'unbounded' AS "maxOccurs"
                                    )
                                    )
                                    )
                                    )
                                    )
                                    )--.EXTRACT('/').GETCLOBVAL() -- This needed if trying to run SQL statement interactively in
                                    -- TOAD. Likewise INTO clause below needs commenting out and
                                    -- p_target_table parameter bound.
                                    INTO l_xmlSchema
                                    FROM (SELECT table_name
                                    , internal_column_id
                                    , CASE
                                    WHEN data_type IN ('VARCHAR2', 'CHAR') THEN
                                    XMLELEMENT
                                    ( "xs:element"
                                    , XMLATTRIBUTES
                                    ( column_name AS "name"
                                    , DECODE (nullable, 'Y', 0, 1) AS "minOccurs"
                                    , column_name AS "xdb:SQLName"
                                    , data_type AS "xdb:SQLTYPE"
                                    )
                                    , XMLELEMENT
                                    ( "xs:simpleType"
                                    , XMLELEMENT
                                    ( "xs:restriction"
                                    , XMLATTRIBUTES ('xs:string' AS "base")
                                    , XMLELEMENT
                                    ( "xs:maxLength"
                                    , XMLATTRIBUTES (data_length AS "value")
                                    )
                                    )
                                    )
                                    )
                                    WHEN data_type = 'DATE' THEN
                                    XMLELEMENT
                                    ( "xs:element"
                                    , XMLATTRIBUTES
                                    ( column_name AS "name"
                                    -- , 'xs:dateTime' AS "type"
                                    , 'xs: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
                                    ( "xs:element"
                                    , XMLATTRIBUTES
                                    ( column_name AS "name"
                                    , DECODE (data_scale, 0, 'xs:integer', 'xs:double') AS "type"
                                    , DECODE (nullable, 'Y', 0, 1) AS "minOccurs"
                                    , column_name AS "xdb:SQLName"
                                    , data_type AS "xdb:SQLTYPE"
                                    )
                                    )
                                    ELSE
                                    XMLELEMENT
                                    ( "xs:element"
                                    , XMLATTRIBUTES
                                    ( column_name AS "name"
                                    , 'xs:anySimpleType' AS "type"
                                    , DECODE (nullable, 'Y', 0, 1) AS "minOccurs"
                                    , column_name AS "xdb:SQLName"
                                    , data_type AS "xdb:SQLTYPE"
                                    )
                                    )
                                    END AS column_xml
                                    FROM user_tab_cols c
                                    WHERE table_name = p_target_table
                                    ORDER BY internal_column_id
                                    )
                                    GROUP BY table_name;

                                    RETURN l_xmlSchema;

                                    END gen_xml_schema;
                                    1 2 Previous Next