Database Software

Hello,

I Have Problem With XMLForest , I am Including Some Of the columns in xml forest , but i also want xml attribute for some of them, so i used xml element inside xml forest but it gives error if don't provide an alias. If Provide alias , display format get changed. If I Use Xml Element Instead of Xmlforest , code length also will increase as well as if the dat is null it doesn't eliminate the Tag.

I have to Finish this as soon as possible . Please Help


Example :

Code :

---------------------------------------------------------------------------------------------------------------------
SELECT
xmlelement
(
name "COMMODITY",
xmlforest
(
c.commodity_id ,
c.commodity_name as COMMODITY_NAME,
xmlelement
(
name COMMODITY_GROUP,
XMLATTRIBUTES ( 'MS_SRA_COMMODITIES_GROUP' AS "LOV_NAME" ),
c.commodity_group
)as COMMODITY_GROUP ,
xmlelement
(
name COMMODITY_GROUP_D,
XMLATTRIBUTES ( 'MS_SRA_COMMODITIES_GROUP' AS "LOV_NAME" ),
MS_APPS_UTILITIES.GET_DISPLAY_VALUE(100000,'MS_SRA_COMMODITIES_GROUP',c.commodity_group)
) as COMMODITY_GROUP_D,
c.valid_from as VALID_FROM
) as sasd
) as asd
FROM ms_sra_commodities c


---------------------------------------------------------------------------------------------------------------------------------

Actual Result :
------------------------------------------------------------------------------------------------------------------------------------------------
<COMMODITY>
<COMMODITY_ID>COMM-100053</COMMODITY_ID>
<COMMODITY_NAME>Commodity -2</COMMODITY_NAME>
- <COMMODITY_GROUP>
<COMMODITY_GROUP LOV_NAME="MS_SRA_COMMODITIES_GROUP">1</COMMODITY_GROUP>
</COMMODITY_GROUP>
- <COMMODITY_GROUP_D>
<COMMODITY_GROUP_D LOV_NAME="MS_SRA_COMMODITIES_GROUP">Direct</COMMODITY_GROUP_D>
</COMMODITY_GROUP_D>
<VALID_FROM>2013-01-22</VALID_FROM>
</COMMODITY>

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Expected Result :

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<COMMODITY>
<COMMODITY_ID>COMM-100053</COMMODITY_ID>
<COMMODITY_NAME>Commodity -2</COMMODITY_NAME>
<COMMODITY_GROUP LOV_NAME="MS_SRA_COMMODITIES_GROUP">1</COMMODITY_GROUP>
<COMMODITY_GROUP_D LOV_NAME="MS_SRA_COMMODITIES_GROUP">Direct</COMMODITY_GROUP_D>
<VALID_FROM>2013-01-22</VALID_FROM>
</COMMODITY>