9 Replies Latest reply: Dec 18, 2012 2:54 AM by BluShadow RSS

    Generate XML from SQL

    Parth Panjabi
      Hi I wan to create XML from SQL using XMLElement and XMLAttribute as per below Format

      *<field name="AccountNumber">TEST01</field>*

      is it possible?

      Thanks in advance

      waiting for yours positive reply
        • 1. Re: Generate XML from SQL
          odie_63
          Hi I wan to create XML from SQL using XMLElement and XMLAttribute as per below Format
          If you already know about SQL/XML functions then why don't you try it out yourself?

          If you're not sure about the syntax, check it in the manual :
          http://www.oracle.com/pls/db112/search?remark=quick_search&word=xmlelement
          • 2. Re: Generate XML from SQL
            BluShadow
            Parth Panjabi wrote:
            Hi I wan to create XML from SQL using XMLElement and XMLAttribute as per below Format

            *<field name="AccountNumber">TEST01</field>*

            is it possible?
            of course it's impossible.... Oracle is only a poor little database... it doesn't know how to handle such complex requests...

            /sarcasm
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select 'TEST01' as accountnumber from dual)
              2  --
              3  -- end of test data
              4  --
              5  select xmlelement("field", xmlattributes('AccountNumber' as "name"), accountnumber)
              6* from t
            SQL> /
            
            XMLELEMENT("FIELD",XMLATTRIBUTES('ACCOUNTNUMBER'AS"NAME"),ACCOUNTNUMBER)
            ----------------------------------------------------------------------------------------
            <field name="AccountNumber">TEST01</field>
            • 3. Re: Generate XML from SQL
              Parth Panjabi
              I am trying to create XML from below Code -

              DECLARE
              qryCtx DBMS_XMLGEN.ctxHandle;
              result1 CLOB;
              BEGIN
              qryCtx := DBMS_XMLGEN.newContext('select XMLElement("field",XMLAttributes(''AccountNumber'' AS "name"),delivery_identity),
              XMLElement("field",XMLAttributes(''CBAccountNumber'' AS "name"),null)
              from ifsapp.invoice_tab
              where trunc(invoice_date) = trunc(sysdate-6)
              and delivery_identity like ''ETH%''
              and company = ''BNS01'' and rownum < 2');
              -- Set the row header to be EMPLOYEE
              DBMS_XMLGEN.setRowSetTag(qryCtx, 'ROOT');
              DBMS_XMLGEN.setRowSetTag(qryCtx, 'ROWS');
              DBMS_XMLGEN.setNullHandling(qryCtx, dbms_xmlgen.EMPTY_TAG);
              DBMS_XMLGEN.setRowTag(qryCtx, 'ROW');
              -- Get the result1
              result1 := DBMS_XMLGEN.getXML(qryCtx);

              --dbms_output.put_line(result1);
              INSERT INTO ethegin_xml VALUES(result1);
              --Close context
              DBMS_XMLGEN.closeContext(qryCtx);
              commit;
              END;
              /

              but it generated below Output :-

              <?xml version="1.0"?>
              <ROWS>
              <ROW>
              <XMLELEMENT_x0028__x0022_FIELD_x0022__x002C_XMLATTRIBUTES_x0028__x0027_ACCOUNTNUMBER_x0027_AS_x0022_NAME_x0022__x0029__x002C_DELIVERY_IDENTITY_x0029_>
              <field name="AccountNumber">ETH263_DTL</field> </XMLELEMENT_x0028__x0022_FIELD_x0022__x002C_XMLATTRIBUTES_x0028__x0027_ACCOUNTNUMBER_x0027_AS_x0022_NAME_x0022__x0029__x002C_DELIVERY_IDENTITY_x0029_>
              <XMLELEMENT_x0028__x0022_FIELD_x0022__x002C_XMLATTRIBUTES_x0028__x0027_CBACCOUNTNUMBER_x0027_AS_x0022_NAME_x0022__x0029__x002C_NULL_x0029_>
              <field name="CBAccountNumber"></field> </XMLELEMENT_x0028__x0022_FIELD_x0022__x002C_XMLATTRIBUTES_x0028__x0027_CBACCOUNTNUMBER_x0027_AS_x0022_NAME_x0022__x0029__x002C_NULL_x0029_>
              </ROW>
              </ROWS>

              it should be as below:-


              <?xml version="1.0"?>
              <ROWS>
              <ROW>
              <field name="AccountNumber">ETH263_DTL</field>
              <field name="CBAccountNumber"></field>
              </ROW>
              </ROWS>

              please help me

              thanks in advance
              • 4. Re: Generate XML from SQL
                odie_63
                DBMS_XMLGEN brings no added value in this case.

                Just use SQL/XML functions all along, e.g.
                INSERT INTO ethegin_xml
                SELECT XMLSerialize(document
                         XMLElement("ROWS",
                           XMLAgg(
                             XMLElement("ROW", 
                               XMLElement("field",
                                 XMLAttributes('AccountNumber' AS "name")
                               , delivery_identity
                               )
                             , XMLElement("field",
                                 XMLAttributes('CBAccountNumber' AS "name")
                               , null
                               )
                             )
                           )
                         )
                       )
                FROM ifsapp.invoice_tab
                WHERE trunc(invoice_date) = trunc(sysdate-6)
                AND delivery_identity like 'ETH%'
                AND company = 'BNS01' 
                and rownum < 2 ;
                • 5. Re: Generate XML from SQL
                  Parth Panjabi
                  Hi,

                  Thank you very much for your Help.its working fine

                  But still I have Doubts,

                  if I requires a carriage return after each field,what can we modify in your code as you mention above?

                  Thanks in Advance
                  • 6. Re: Generate XML from SQL
                    BluShadow
                    Parth Panjabi wrote:
                    Hi,

                    Thank you very much for your Help.its working fine

                    But still I have Doubts,

                    if I requires a carriage return after each field,what can we modify in your code as you mention above?

                    Thanks in Advance
                    XML doesn't require a newline after each element to be valid XML. Adding newlines and whitespace to XML just makes the XML file larger unnecessarily. If you want to see the XML "pretty printed" simply load the file into a web browser and let it do the formatting for you.
                    • 7. Re: Generate XML from SQL
                      Solomon Yakobson
                      Parth Panjabi wrote:
                      Thanks in Advance
                      Why don't you open Oracle docs and read it. If you want to beautify, XMLSerialize supports INDENT:
                      SQL> SELECT XMLSerialize(document
                        2           XMLElement("ROWS",
                        3             XMLAgg(
                        4               XMLElement("ROW", 
                        5                 XMLElement("field",
                        6                   XMLAttributes('AccountNumber' AS "name")
                        7                 , ename
                        8                 )
                        9               , XMLElement("field",
                       10                   XMLAttributes('CBAccountNumber' AS "name")
                       11                 , null
                       12                 )
                       13               )
                       14             )
                       15           )
                       16         )
                       17  FROM EMP
                       18  WHERE deptno = 10
                       19  /
                      
                      XMLSERIALIZE(DOCUMENTXMLELEMENT("ROWS",XMLAGG(XMLELEMENT("ROW",XMLELEMENT("FIELD
                      --------------------------------------------------------------------------------
                      <ROWS><ROW><field name="AccountNumber">CLARK</field><field name="CBAccountNumber
                      "></field></ROW><ROW><field name="AccountNumber">KING</field><field name="CBAcco
                      untNumber"></field></ROW><ROW><field name="AccountNumber">MILLER</field><field n
                      ame="CBAccountNumber"></field></ROW></ROWS>
                      
                      
                      SQL> SELECT XMLSerialize(document
                        2           XMLElement("ROWS",
                        3             XMLAgg(
                        4               XMLElement("ROW", 
                        5                 XMLElement("field",
                        6                   XMLAttributes('AccountNumber' AS "name")
                        7                 , ename
                        8                 )
                        9               , XMLElement("field",
                       10                   XMLAttributes('CBAccountNumber' AS "name")
                       11                 , null
                       12                 )
                       13               )
                       14             )
                       15           )
                       16          INDENT
                       17         )
                       18  FROM EMP
                       19  WHERE deptno = 10
                       20  /
                      
                      XMLSERIALIZE(DOCUMENTXMLELEMENT("ROWS",XMLAGG(XMLELEMENT("ROW",XMLELEMENT("FIELD
                      --------------------------------------------------------------------------------
                      <ROWS>
                        <ROW>
                          <field name="AccountNumber">CLARK</field>
                          <field name="CBAccountNumber"/>
                        </ROW>
                        <ROW>
                          <field name="AccountNumber">KING</field>
                          <field name="CBAccountNumber"/>
                        </ROW>
                        <ROW>
                          <field name="AccountNumber">MILLER</field>
                      
                      XMLSERIALIZE(DOCUMENTXMLELEMENT("ROWS",XMLAGG(XMLELEMENT("ROW",XMLELEMENT("FIELD
                      --------------------------------------------------------------------------------
                          <field name="CBAccountNumber"/>
                        </ROW>
                      </ROWS>
                      
                      
                      SQL> 
                      {code}
                      
                      SY.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                      • 8. Re: Generate XML from SQL
                        sharavs
                        hi,

                        with testt as (select 'TEST01' as accountnumber from dual)

                        select xmlelement("field", xmlattributes('AccountNumber' as "name"), accountnumber) as result
                        from testt;
                        o/p:
                        RESULT
                        <field name="AccountNumber">TEST01</field>
                        • 9. Re: Generate XML from SQL
                          BluShadow
                          sharavs wrote:
                          hi,

                          with testt as (select 'TEST01' as accountnumber from dual)

                          select xmlelement("field", xmlattributes('AccountNumber' as "name"), accountnumber) as result
                          from testt;
                          o/p:
                          RESULT
                          <field name="AccountNumber">TEST01</field>
                          Good to see you've read the rest of the thread and provided something new and helpful for the OP.... :-/