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
– Aluan Haddad, Commented May 14, 2017 at 5:59
@AluanHaddad there can be multiple subject and marks for students and some times no marks. So I am using left join — Sachu
– Sachu, Commented May 14, 2017 at 6:01
Students as Student left join Marks as Mark on .... group by ... — Aluan Haddad
– Aluan Haddad, Commented May 14, 2017 at 6:02

Community · Accepted Answer · 2017-05-23 12:26:09Z

1

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.)

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered May 14, 2017 at 10:05

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to generate the xml from oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related