7 Replies Latest reply: Sep 18, 2012 4:38 PM by odie_63 RSS

    Creating XML Schema from tables With Constraints

    958449
      Greetings,

      I'd have an interesting question. I finally am getting familiar with the various kinds of xml solutions provided by the oracle database, but hey here I have another interesting question I can't seem to get into life. I'm currently generating XML Schemas (XSD) from the tables of my database. Its nice its cool however I'd need to have the table's constraints with the xsd:elements also. And heres my problem, I can't seem to insert the table constrains. I'll show you what I mean:
      <xsd:element name="MESSAGE_TABLE">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="MESSAGE_RECORD">
              <xsd:complexType>
                <xsd:all>
                  <xsd:element name="ID" type="xsd:integer" minOccurs="1" />
                  <xsd:element name="HEADER">
                    <xsd:simpleType>
                      <xsd:restriction base="xsd:string>
                        <xsd:maxLength value="255" />
                      </xsd:restriction>
                    </xsd:simpleType>
                  </xsd:element>
                </xsd:all>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
        <!-- I'd need some more things here like... -->
      
        <!-- Primary key(s) -->
        <xsd:key name="PK_ID_PRIM">
          <xsd:selector xpath="." />
          <xsd:field xpath="ID" />
        </xsd:key>
      
        <!-- Foreign key(s) -->
        <xsd:keyref name="FK_HEADER_FOREIGN" refer="PK_HEADER_ID">
          <xsd:selector xpath="HEADER" />
          <xsd:field xpath="ID" />
        </xsd:keyref>
      
        <!-- Unique constraint(s) -->
        <xsd:unique name="UQ_..." ... />
          ....
        </xsd:unique>
      </xsd:element>
      That would fit my business needs, however I may be so blind that I can't see the forest from the tree. Currently I got so far that:
                xmlElement
                (
                    "xsd:schema",
                    xmlAttributes
                    (
                        'http://www.w3.org/2001/XMLSchema'      as "xmlns:xsd"
                    ),
                    xmlElement
                    (
                      "xsd:element",
                      xmlAttributes
                      (
                        target_table                            as "name"
                      ),
                      xmlElement                
                      (
                        "xsd:complexType",
                        xmlElement
                        (
                          "xsd:sequence",
                          xmlElement
                          (
                            "xsd:element",
                            xmlAttributes
                            (
                              target_table || '_RECORD'         as "name",
                              'unbounded'                       as "maxOccurs"
                            ),
                            xmlElement
                            (
                              "xsd:complexType",
                              xmlElement
                              (
                                "xsd:sequence",
                                (
                                  xmlAgg(ELEMENT)
                                )
                              )
                            )
                          )
                        )
                      ),
                      xmlElement
                      (
                        "xsd:Key",
                        
                      )
                    )
                  )
      As you can see this won't be good since I have put a single xml element in there. I guess I'd need something more like an xmlAgg(CONSTRAINTS), however in that case I'm wondering how will the select's FROM part look like.
        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"
                        ),
                        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:date' as "type",
                          decode(NULLABLE, 'Y', 0, 1) as "minOccurs"
                        )
                      )
                    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"
                        )
                      )
                    ELSE
                      xmlElement
                      (
                        "xsd:element",
                        xmlattributes
                        (
                          column_name as "name",
                          'xsd:anySimpleType' as "type",
                          decode(NULLABLE, 'Y', 0, 1) as "minOccurs"
                        )
                      )
          end ELEMENT
          FROM user_tab_cols c
          WHERE TABLE_NAME = target_table
          ORDER BY internal_column_id
        )
        GROUP BY TABLE_NAME;
      Thank you very much for all your help!

      Regards,
      Joey

      Edited by: Wrath#87 on 2012.09.05. 22:15
        • 1. Re: Creating XML Schema from tables With Constraints
          Jason_(A_Non)
          Just to offer an alternative before exploring your method deeper, what about using the
          [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414]dbms_metadata.get_xxx methods to have Oracle retrieve the information for you. If needed, you could apply a stylesheet to the results, using XMLTransform to alter the format.
          • 2. Re: Creating XML Schema from tables With Constraints
            Marco Gralike
            Regarding possibilities with DBMS_METADATA in the XML context, have a look here

            http://www.liberidu.com/blog/2008/03/19/using-xmldiff-for-database-change-management/

            Or discussions on generating and consuming such data

            How to find the compare and identify between xmldocs
            • 3. Re: Creating XML Schema from tables With Constraints
              odie_63
              As you can see this won't be good since I have put a single xml element in there. I guess I'd need something more like an xmlAgg(CONSTRAINTS), however in that case I'm wondering how will the select's FROM part look like.
              Typically, that is achieved with an inline correlated subquery.
              Example for the PK :
              ...
              , (
                  SELECT xmlelement("xsd:key", xmlattributes(uc.constraint_name as "name")
                         , xmlelement("xsd:selector", xmlattributes('.' as "xpath"))
                         , xmlagg(
                             xmlelement("xsd:field", xmlattributes(ucc.column_name as "xpath"))
                             order by ucc.position
                           )
                         )
                  FROM user_constraints uc
                       JOIN user_cons_columns ucc ON ucc.constraint_name = uc.constraint_name 
                  WHERE uc.table_name = :target_table
                  AND uc.constraint_type = 'P'
                  GROUP BY uc.constraint_name 
                )
              ...
              You may want to consider Jason's comment too. It's a valid alternative.
              DBMS_METADATA can get you pretty much all the info you need in XML format with zero effort.
              Then you can put some more time in building a robust XSL stylesheet to transform it to an XML schema.
              • 4. Re: Creating XML Schema from tables With Constraints
                958449
                Thanks for that, answer. That helped me a lot managing primary constraints. However I still have problems managing foreign keys. I come up with the following formula:
                          xmlElement
                          (
                              "xsd:schema",
                              xmlAttributes
                              (
                                  'http://www.w3.org/2001/XMLSchema'      as "xmlns:xsd"
                              ),
                              xmlElement
                              (
                                  "xsd:element",
                                  xmlAttributes
                                  (
                                      upper(target_table) as "name"
                                  ),
                                  xmlElement
                                  (
                                      "xsd:complexType",
                                      xmlElement
                                      (
                                        "xsd:all",
                                        (
                                            xmlAgg(ELEMENT)
                                        )
                                      )
                                  ),
                                  (
                                      SELECT    xmlElement
                                                (
                                                  "xsd:key",
                                                  xmlattributes
                                                  (
                                                    uc.constraint_name as "name"
                                                  ),
                                                  xmlElement
                                                  (
                                                    "xsd:selector",
                                                    xmlattributes
                                                    (
                                                      '.' as "xpath"
                                                    )
                                                  ),
                                                  xmlAgg
                                                  (
                                                    xmlElement
                                                    (
                                                      "xsd:field",
                                                      xmlattributes
                                                      (
                                                        ucc.column_name as "xpath"
                                                      )
                                                    )
                                                    order by ucc.position
                                                  )
                                                )
                                      FROM      user_constraints uc
                                                JOIN      user_cons_columns ucc
                                                ON        ucc.constraint_name   =   uc.constraint_name
                                      WHERE     uc.table_name                   =   upper(target_table)
                                      AND       uc.constraint_type              =   'P'
                                      GROUP BY  uc.constraint_name
                                  ),
                                  (
                                      SELECT    xmlElement
                                                (
                                                    "xsd:keyRef",
                                                    xmlattributes
                                                    (
                                                        a.constraint_name as "name"
                                                    ),
                                                    xmlElement
                                                    (
                                                        "xsd:selector",
                                                        xmlattributes
                                                        (
                                                            c.table_name as "xpath"
                                                        )
                                                    ),
                                                    xmlAgg
                                                    (
                                                        xmlElement
                                                        (
                                                            "xsd:field",
                                                            xmlattributes
                                                            (
                                                                d.column_name as "xpath"
                                                            )
                                                        )
                                                        order by  c.table_name
                                                    )
                                                )
                                      FROM      all_constraints   a, 
                                                all_cons_columns  b, 
                                                all_constraints   c, 
                                                all_cons_columns  d
                                      WHERE     a.constraint_name   =   b.constraint_name
                                      AND       a.constraint_name   =   c.r_constraint_name
                                      AND       c.constraint_name   =   d.constraint_name
                                      AND       a.table_name        =   upper(target_table)
                                  )
                              )
                          )
                This gives me the following error message:
                 00937. 00000 -  "not a single-group group function" 
                • 5. Re: Creating XML Schema from tables With Constraints
                  odie_63
                  .

                  Edited by: odie_63 on 18 sept. 2012 20:31
                  • 6. Re: Creating XML Schema from tables With Constraints
                    Jason_(A_Non)
                    This is where you stop and compare what you wrote against what Marc (odie_63) wrote. What piece of SELECT statement syntax does he use that you do not? Your error message gives a very good clue as to what piece of syntax is.

                    Additional thoughts. In his example, he used [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4302.htm]user_constraints and you used [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm]all_constraints. You will want to learn the difference as it can get you bad data if two tables in the DB share the same name and you are using all_constraints. You will also want to understand what CONSTRAINT_TYPES are being returned by your query, compared to what Marc's query returned.

                    Is there a strong reason you continue on this way instead of using DBMS_METADATA and using a stylesheet to transform those results into the format you need?

                    Edited by: A_Non on Sep 18, 2012 1:05 PM
                    Forgot to add this documentation link
                    [url http://www.oracle.com/technetwork/indexes/documentation/index.html]Oracle Documentation Apparently we forgot to ask for your version so I went with the link to all the documentation.
                    • 7. Re: Creating XML Schema from tables With Constraints
                      odie_63
                      Something I left behind last time...

                      The xsd:selector/@xpath attribute defines an XPath expression that is relative to the parent of the key element.
                      So, in the present case, if the key is declared under the root element, e.g. <TARGET_TABLE>, then the selector must point to the record element, i.e. <TARGET_TABLE_RECORD>.


                      A few other remarks about the xsd:keyref :

                      - Declaring a keyref makes sense only if the table has a self-referential integrity constraint
                      - You're also missing the refer attribute in your code

                      Here's a sample query that generates the list of keyrefs for a table.
                      Note the additional filter on the constraint's table name :
                      SELECT xmlagg(
                               xmlelement("xsd:keyref",
                                 xmlattributes(
                                   uc.constraint_name as "name"
                                 , uc.r_constraint_name as "refer"  
                                 )
                               , xmlelement("xsd:selector", 
                                   xmlattributes(
                                     :target_table || '_RECORD' as "xpath"
                                   )
                                 )
                               , xmlagg(
                                   xmlelement("xsd:field", 
                                     xmlattributes(
                                       ucc.column_name as "xpath"
                                     )
                                   )
                                   order by ucc.position
                                 )
                               )
                             )
                      FROM user_constraints uc
                           JOIN user_cons_columns ucc 
                             ON ucc.constraint_name = uc.constraint_name
                      WHERE uc.table_name = :target_table
                      AND uc.constraint_type = 'R'
                      AND EXISTS (
                        SELECT null
                        FROM user_constraints ucp
                        WHERE ucp.constraint_name = uc.r_constraint_name
                        AND ucp.table_name = uc.table_name
                      )
                      GROUP BY uc.constraint_name
                             , uc.r_constraint_name
                      ;