I can't apply CASE WHEN THEN, when creating XML.
maybe I should use IF ELSE, but I'm creating a regular query.
I should have a condition:

CASE WHEN 1 2=true
 THEN attribute "A"
 ELSE attribute "B"
END

Example:

SELECT XMLROOT(               
               XMLELEMENT("attrib",                          
                          CASE
                            WHEN (1   2) = 3 THEN
                             (XMLATTRIBUTES('a' as "A"))
                            ELSE
                             (XMLATTRIBUTES('b' as "B"))                          
                          END))
  FROM dual

CodePudding user response：

From the XMLELEMENT documentation:

The objects that make up the element content follow the XMLATTRIBUTES keyword. In the XML_attributes_clause, if the value_expr is null, then no attribute is created for that value expression.

You want to put the CASE expression inside XMLATTRIBUTES and have one for each attribute:

select XMLROOT(
         XMLELEMENT("attrib",
           XMLATTRIBUTES(
             CASE WHEN 1   2 =  3 THEN 'a' END AS "A",
             CASE WHEN 1   2 <> 3 THEN 'b' END AS "B"
           )
         ),
         VERSION '1.0'
       ) AS xml
FROM   DUAL;

Note: You also need the VERSION argument for XMLROOT.

Which outputs:

XML
<?xml version="1.0"?> <attrib A="a"/>

db<>fiddle here

CodePudding user response：

Because

• The CASE..WHEN expression is stated at the wrong location
• XMLROOT function has missing arguments
• The single quotes should wrap the identifiers(a,A,b,B) rather than double quotes The EVALNAME keywords preceding 'A' and 'B' are missing

you can rearrange the existing statement as below considering those facts;

SELECT XMLROOT(CASE
                 WHEN 1   2 = 3 THEN
                  XMLELEMENT("attrib", XMLATTRIBUTES('a' AS EVALNAME 'A'))
                 ELSE
                  XMLELEMENT("attrib", XMLATTRIBUTES('b' AS EVALNAME 'B'))
               END,
               VERSION '1.0',
               STANDALONE YES)
  FROM dual;

But XMLROOT is deprecated, and XMLSERIALIZE function is recommended by Oracle rather than that. So, use the following as alternative to your case

SELECT XMLType(CASE
                 WHEN 1   2 = 3 THEN
                   XMLSERIALIZE(
                       CONTENT XMLELEMENT("attrib", XMLATTRIBUTES('a' AS EVALNAME 'A'))
                    VERSION '1.0'
                   )
                ELSE
                   XMLSERIALIZE(
                       CONTENT XMLELEMENT("attrib", XMLATTRIBUTES('b' AS EVALNAME 'B'))
                    VERSION '1.0'
                   )                  
                 END).getStringVal()            
  FROM dual;