1 Reply Latest reply: Nov 28, 2012 6:49 AM by odie_63 RSS

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

      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">*

      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

          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(
                     , XMLForest(
                         e.first_name as "First_Name"
                       , e.last_name  as "Last_Name"
                 ) AS "RESULT"
          FROM hr.employees e