how to generate the xml from oracle

Question

I have two tables as below

Table students

PersonID     firstName 
1            John
2            Mike

Table Marks

mark_id     person_id   subject   marks
1           1           English    50
2           1           Maths      75

the below query give the xml as below

select XMLElement(      "Students" ,
              (select XMLAgg( XMLElement("Student" ,                      
                             XmlElement( "Student_id","id")  ,
                             XmlElement(  "First_Name","firstname")  
                             ))
                             FROM students  )
                             )
                               AS "RESULT" 
From dual

<Students>
<Student>
<Student_id>1</Student_id>
<First_Name>John</First_Name>
</Student>
<Student>
<Student_id>2</Student_id>
<First_Name>Mike</First_Name>
</Student>
<Students>

Now I need to do a left join the table students with Marks and create a xml like below

<Students>
    <Student>
    <Student_id>1</Student_id>
    <First_Name>John</First_Name>
    <Marks>
     <subject>English</subject>
     <mark>50</mark>
     <subject>Maths</subject>
     <mark>75</mark>
    </Marks>
    </Student>
    <Student>
    <Student_id>2</Student_id>
    <First_Name>Mike</First_Name>
    <Marks/>
    </Student>
    <Students>

how can i achieve if i write the select query it will return two rows for the first student since it has 2 rows to join in the second mark table.

Depends on what the requirements are. If there are multiple marks and there should only be one row per student, you can use a `group by` clause. — Aluan Haddad, May 14 '17 at 05:59
@AluanHaddad there can be multiple subject and marks for students and some times no marks. So I am using left join — Sachu, May 14 '17 at 06:01
`Students as Student left join Marks as Mark on .... group by ...` — Aluan Haddad, May 14 '17 at 06:02

score 1 · Accepted Answer · edited May 23 '17 at 12:26

select XMLElement(
         "Students" ,
         XMLAgg(
           XMLElement(
             "Student" ,
             XMLFOREST(
               s."Student_id" AS "id",
               s."First_Name" AS "firstname"
             ),
             m.marks
           )
         )
       )
FROM   students s
       LEFT OUTER JOIN
       ( SELECT person_id,
                XMLELEMENT(
                  "Marks",
                  XMLAGG(
                    XMLFOREST(
                      "subject",
                      "marks" AS "mark"
                    )
                  )
                ) AS marks
         FROM   Marks
       ) m
       ON ( s."PersonID" = m.person_id )

(Note: unless there is a specific business need, it is not good practice to use quoted identifiers for table/column names; just use unquoted identifiers everywhere and let oracle manage the case (in)sensitivity.)

how to generate the xml from oracle

1 Answers1