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