1 Reply Latest reply: Jan 22, 2013 6:10 AM by odie_63 RSS

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

    986298
      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
          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