8 Replies Latest reply: Nov 5, 2012 5:45 AM by odie_63 RSS

    XML generation

    INRi
      Hi All,
      I want to generate my table data in xml from sql/plsql.
      My xml format -

      <Employee>
      <Emp_Attribute col_name="Empno" col_val="123"/>
      <Emp_Attribute col_name="Ename" col_value="Sam"/>
      </Employee>
        • 1. Re: XML generation
          jeneesh
          Like..

          SELECT XMLElement("Emp", 
                             XMLElement("name", e.first_name ||' '|| e.last_name),
                             XMLElement("hiredate", e.hire_date)) AS "RESULT" 
          FROM hr.employees e ;
          Refer Docs
          • 2. Re: XML generation
            INRi
            No,its wrong.Not satisfied to my requirement.
            • 3. Re: XML generation
              odie_63
              SQL> select xmlelement("Employee",
                2           xmlelement("Emp_Attribute", xmlattributes('Empno' as "col_name", empno as "col_val"))
                3         , xmlelement("Emp_Attribute", xmlattributes('Ename' as "col_name", ename as "col_val"))
                4         ) as result
                5  from scott.emp
                6  where rownum = 1
                7  ;
              
              RESULT
              --------------------------------------------------------------------------------
              <Employee><Emp_Attribute col_name="Empno" col_val="7369"></Emp_Attribute><Emp_At
              tribute col_name="Ename" col_val="SMITH"></Emp_Attribute></Employee>
              • 4. Re: XML generation
                lee200
                Something like this:
                SELECT XMLElement("Employee", 
                                  XMLElement("Emp_Attribute", XMLAttributes('Empno' "col_name", e.empno "col_val")) ,
                                  XMLELEMENT("Emp_Attribute", XMLAttributes('Ename' "col_name", e.ename "col_val"))) result_xml
                FROM   emp e
                • 5. Re: XML generation
                  Stew Ashton
                  What jeneesh gave you is actually much better than your requirement. You asked for:
                  <Employee>
                  <Emp_Attribute col_name="Empno" col_val="123"/>
                  <Emp_Attribute col_name="Ename" col_value="Sam"/>
                  </Employee>
                  This is the most meaningless way to format the data.

                  You should have a tag called "Empno" that you can define as being an integer, then a tag call "Ename" that you can define as a string. Such definitions are contained in the XMl Schema declaration, when you have one.

                  What you asked for is a generic (and therefore meaningless) attribute / value pairing. This prevents anyone from knowing the datatypes of the values involved.

                  This kind of generic attribute / value pairing is anti-relational, and it is also anti-XML. It arbitrarily and unnecessarily removes structure from structured data.

                  If you cannot change your requirement, here is what you asked for:
                  select xmlserialize(content xmlelement("Employee",
                    xmlelement(
                      "Emp_Attribute",
                      xmlattributes('Empno' as "col_name", empno as "col_value")
                    ),
                    xmlelement(
                      "Emp_Attribute",
                      xmlattributes('Ename' as "col_name", ename as "col_value")
                    )
                  ) indent)
                  from scott.emp
                  where rownum = 1;
                  
                  <Employee>
                    <Emp_Attribute col_name="Empno" col_value="7369"/>
                    <Emp_Attribute col_name="Ename" col_value="SMITH"/>
                  </Employee>
                  • 6. Re: XML generation
                    odie_63
                    Stew Ashton wrote:
                    This kind of generic attribute / value pairing is anti-relational, and it is also anti-XML. It arbitrarily and unnecessarily removes structure from structured data.
                    At least OP didn't ask for something like this :
                    <Employee>
                      <Emp_Attribute1 col_name="Empno" col_val="123"/>
                      <Emp_Attribute2 col_name="Ename" col_value="Sam"/>
                      <Emp_Attribute3 ...
                    • 7. Re: XML generation
                      Billy~Verreynne
                      Stew Ashton wrote:

                      This kind of generic attribute / value pairing is anti-relational, and it is also anti-XML. It arbitrarily and unnecessarily removes structure from structured data.
                      Agree fully. XML needs to be correctly used.

                      @OP: suggest you relook at Janeesh and Stew's suggestions - as these are sensible and not hacks like you are trying to implement.
                      • 8. Re: XML generation
                        Stew Ashton
                        odie_63 wrote:
                        At least OP didn't ask for something like this :
                        <Employee>
                        <Emp_Attribute1 col_name="Empno" col_val="123"/>
                        <Emp_Attribute2 col_name="Ename" col_value="Sam"/>
                        <Emp_Attribute3 ...
                        Excellent point! Another glass half full ;)