XML Attributes for columns generated using XMLForest

From: Leo J. Hart IV <leo.hart_at_nospam.fmr.com>
Date: Tue, 24 Jun 2003 09:53:15 -0400
Message-ID: <HIYJa.154$Mn.182@news-srv1.fmr.com>

Hello,

I am using the following SQLX query:

    SELECT XMLELEMENT

         ("MOR_DIM_DS",
       XMLATTRIBUTES('DIM_DS_ID' AS "primaryKeyColumn",
            'DIM_DS_ID_SEQ' AS "primaryKeySequence"),
       XMLFOREST
        ((SELECT XMLELEMENT


            ("MOR_DATASOURCE",

             XMLATTRIBUTES('DATASOURCE_ID' AS "primaryKeyColumn",
               'DATASOURCE_ID_SEQ' AS "primaryKeySequence"
                 ),
             XMLFOREST
                (XML_UTIL_PKG.XML_ESCAPE(DATASOURCE_ID) AS "DATASOURCE_ID",
              XML_UTIL_PKG.XML_ESCAPE(DATASOURCE_NAME) AS "DATASOURCE_NAME"
                )
            ) AS "MOR_DATASOURCE"
          FROM  MOR_DATASOURCE MOR_DATASOURCE1
          WHERE  MOR_DATASOURCE1.DATASOURCE_ID = MOR_DIM_DS0.DATASOURCE_ID)
AS "DATASOURCE_ID",
         (SELECT XMLELEMENT


            ("MOR_DIMENSION",

             XMLATTRIBUTES('DIMENSION_ID' AS "primaryKeyColumn",
               'DIMENSION_ID_SEQ' AS "primaryKeySequence"
                 ),
             XMLFOREST
             (XML_UTIL_PKG.XML_ESCAPE(DIMENSION_ID) AS "DIMENSION_ID",
              XML_UTIL_PKG.XML_ESCAPE(DIMENSION_NAME) AS "DIMENSION_NAME",
              XML_UTIL_PKG.XML_ESCAPE(DIMENSION_SUFFIX) AS
"DIMENSION_SUFFIX",
              XML_UTIL_PKG.XML_ESCAPE(FROM_DB) AS "FROM_DB"
             )
            ) AS "MOR_DIMENSION"
          FROM  MOR_DIMENSION MOR_DIMENSION1
          WHERE  MOR_DIMENSION1.DIMENSION_ID = MOR_DIM_DS0.DIMENSION_ID) AS
"DIMENSION_ID",
         XML_UTIL_PKG.XML_ESCAPE(DIM_DS_ID) AS "DIM_DS_ID"
        )
         ) AS "MOR_DIM_DS"

    FROM MOR_DIM_DS MOR_DIM_DS0
    WHERE DIM_DS_ID = 1 to generate the following results:

    <MOR_DIM_DS primaryKeyColumn="DIM_DS_ID" primaryKeySequence="DIM_DS_ID_SEQ">

     <DATASOURCE_ID>
      <MOR_DATASOURCE primaryKeyColumn="DATASOURCE_ID"
          primaryKeySequence="DATASOURCE_ID_SEQ">
       <DATASOURCE_ID>1</DATASOURCE_ID>
       <DATASOURCE_NAME>RORGTLE1.CORERPTG</DATASOURCE_NAME>
      </MOR_DATASOURCE>
     </DATASOURCE_ID>
     <DIMENSION_ID>
      <MOR_DIMENSION primaryKeyColumn="DIMENSION_ID"
            primaryKeySequence="DIMENSION_ID_SEQ">
       <DIMENSION_ID>1</DIMENSION_ID>
       <DIMENSION_NAME>Accounts</DIMENSION_NAME>
       <DIMENSION_SUFFIX>-AC</DIMENSION_SUFFIX>
       <FROM_DB>Y</FROM_DB>
      </MOR_DIMENSION>
     </DIMENSION_ID>
     <DIM_DS_ID>1</DIM_DS_ID>

    </MOR_DIM_DS>

This XML document represents a row in a table (MOR_DIM_DS) that has foreign key values pointing to a row in MOR_DATASOURCE and a row in MOR_DIMENSION. With this data I will be able to effectively recreate this entity in a separate, but identical database from the ground up.

My goal is to get the following results:

    <MOR_DIM_DS primaryKeyColumn="DIM_DS_ID" primaryKeySequence="DIM_DS_ID_SEQ">

     <DATASOURCE_ID>
      <MOR_DATASOURCE primaryKeyColumn="DATASOURCE_ID"
          primaryKeySequence="DATASOURCE_ID_SEQ">
       <DATASOURCE_ID dataType="NUMBER">1</DATASOURCE_ID>
       <DATASOURCE_NAME
dataType="VARCHAR2">RORGTLE1.CORERPTG</DATASOURCE_NAME>
      </MOR_DATASOURCE>
     </DATASOURCE_ID>
     <DIMENSION_ID>
      <MOR_DIMENSION primaryKeyColumn="DIMENSION_ID"
            primaryKeySequence="DIMENSION_ID_SEQ">
       <DIMENSION_ID dataType="NUMBER">1</DIMENSION_ID>
       <DIMENSION_NAME dataType="VARCHAR2">Accounts</DIMENSION_NAME>
       <DIMENSION_SUFFIX dataType="VARCHAR2">-AC</DIMENSION_SUFFIX>
       <FROM_DB dataType="VARCHAR2">Y</FROM_DB>
      </MOR_DIMENSION>
     </DIMENSION_ID>
     <DIM_DS_ID dataType="NUMBER">1</DIM_DS_ID>

    </MOR_DIM_DS>

You'll notice in this version of the XML document each column has a dataType attribute. I want to include this attribute mainly to handle DATE columns. When using this document to insert rows into the destination database, I need to know when a particular column is a DATE column, so I can format it using TO_DATE().

Now, ignoring the actual value of that attribute (I've got that part figured out), I was wondering how I can create a document like this given that the XMLForest SQLX command does not support attributes. Would I need to nest a bunch of XMLELEMENTs together. If so, could someone with experience doing this help me out with an example? I'm fairly new to XML within Oracle databases and could really use some help.

Thanks so much for your time,
Leo Hart Received on Tue Jun 24 2003 - 08:53:15 CDT