This discussion is archived
1 Reply Latest reply: Nov 28, 2012 4:49 AM by odie_63 RSS

Generate Query in PLSQL to return Well Formed XML with Multiple records

975701 Newbie
Currently Being Moderated
Hi there

This is very urgent. I am trying to create a PLSQL query that should retrieve all records from oracle database table "tbl_Emp" in a well formed xml format. The format is given below

*<Employees xmlns="http://App.Schemas.Employees" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">*
*<Employee>*
*<First_Name></First_Name>*
*<Last_Name></Last_Name>*
*</Employee>*
*<Employee>*
*<First_Name></First_Name>*
*<Last_Name></Last_Name>*
*</Employee>*
*...*
*</Employees>*

To retrieve data in above format, I have been trying to create a query for long time as below

SELECT XMLElement("Employees",
XMLAttributes('http://App.Schemas.Employees' AS "xmlns",
*'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"),*
XMLElement("Employee", XMLForest(First_Name, Last_Name)))
AS "RESULT"
FROM tbl_Emp;

But it does not give me the required output. It creates <Employees> tag with each individual record which I don't need. I need <Employees> tag to be the root tag and <Employee> tag to repeat and wrap each individual record. Please help me in this as this is very urgent. Thanks.
  • 1. Re: Generate Query in PLSQL to return Well Formed XML with Multiple records
    odie_63 Guru
    Currently Being Moderated
    Hi,

    Please remember that nothing is "urgent" here, and repeating that it is will likely produce the opposite effect.

    If you need a quick answer, provide all necessary details in the first place :
    - db version
    - test case with sample data and DDL

    That being said, this one's easy, you have to aggregate using XMLAgg :
    SELECT XMLElement("Employees"
           , XMLAttributes(
               'http://App.Schemas.Employees' AS "xmlns"
             , 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"
             )
           , XMLAgg(
               XMLElement("Employee"
               , XMLForest(
                   e.first_name as "First_Name"
                 , e.last_name  as "Last_Name"
                 )
               )
             )
           ) AS "RESULT"
    FROM hr.employees e
    ;

Legend

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