This discussion is archived
8 Replies Latest reply: Nov 5, 2012 3:45 AM by odie_63 RSS

XML generation

INRi Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    No,its wrong.Not satisfied to my requirement.
  • 3. Re: XML generation
    odie_63 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points