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

    975701
      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
          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
          ;