This discussion is archived
1 Reply Latest reply: Jan 22, 2013 4:10 AM by odie_63 RSS

If XML Element in Placed Inside XML Forest it asks for Alias

986298 Newbie
Currently Being Moderated
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>
  • 1. Re: If XML Element in Placed Inside XML Forest it asks for Alias
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Looks like you already have your answer.
    XMLAttributes must be used within XMLElement, and the latter must be aliased when used within XMLForest : that's the way it works.

    If you want no tag for NULL values, use a CASE statement, e.g.
    SELECT xmlelement("COMMODITY",
             xmlforest( 
               c.commodity_id 
             , c.commodity_name
             )
           , case when c.commodity_group is not null then
               xmlelement("COMMODITY_GROUP", 
                 xmlattributes ( 'MS_SRA_COMMODITIES_GROUP' AS "LOV_NAME" )
               , c.commodity_group
               )
             end
           , xmlelement("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)
             )
           , xmlforest(c.valid_from)
           ) as asd
    FROM ms_sra_commodities c

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points