3 Replies Latest reply on Jun 4, 2020 12:04 PM by 4050852

    Adding [CDATA[ element in an XML

    4050852

      Hi,

      I was asked to generate an XML, extracting data from a table, but adding the [CDATA[ element.

      I have done in the following manner:

       

       

      select   XMLTYPE
                (
                 replace(
                 replace(replace(xmlelement("TAG1",
                 xmlelement("TAG2",col1),
                 xmlelement("TAGX",
                 xmlelement("TAG3",
                 xmlelement("TAG4",col2)))).extract('/*') ,'/TAGX',']]'),'TAGX','![CDATA[<?xml version="1.0" encoding="UTF-8" standalone="yes"?'),
                 '<]]>',']]>')
                )
      into XMLdoc
      from MYTABLE;
      

       

      where col1 and col2 are MYTABLE's columns

       

       

      Is it possible to do in a smarter way instead of using the work-around of the REPLACE?

       

       

      The result should be this:

       

       

      <TAG1>
       <TAG2> 15 </TAG2>
       <![CDATA[<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
        <TAG3>
         <TAG4> Rodgers </TAG4>
        </TAG3>
       ]]>
      </TAG1>
      

       

      Thank you!

        • 1. Re: Adding [CDATA[ element in an XML
          Solomon Yakobson

          with mytable as (select 15 col1,'Rogers' col2 from dual)

          select  xmlelement(

                             "TAG1",

                             xmlelement("TAG2",col1),

                             xmlcdata(

                                      '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' ||

                                      xmlelement(

                                                 "TAG3",

                                                 xmlelement("TAG4",col2)

                                                )

                                     )

                            ) xmldoc

            from  mytable

          /

           

          XMLDOC

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

          <TAG1><TAG2>15</TAG2><![CDATA[<?xml version="1.0" encoding="UTF-8" standalone="y

          es"?><TAG3><TAG4>Rogers</TAG4></TAG3>]]></TAG1>

           

          SQL>

           

          SY.

          • 2. Re: Adding [CDATA[ element in an XML
            cormaco

            You have already asked the same question here and got a lot of responses:

            How to create XML with <![CDATA[<?xml

            • 3. Re: Adding [CDATA[ element in an XML
              4050852

              Thank you very much Solomon.

              One more question

              I see that I can't have the line <![CDATA[<?xml version="1.0" encoding="UTF-8" standalone="yes"?> on a new line.

              I have tried to put the .extract('/*') in every place but it doesn't seem to work . How can I get every TAG on a new line for a more readable XML?

               

              Mark