Generate XML element from Pl/SQL

The idea of this post comes from a successor or my previous post.

http://soa-bpel-esb.blogspot.in/2014/06/events-in-oracle-e-business-suite.html

In this post we saw how we can raise an event from workflow and from SQL and subscribe to it. Now the next step is how we can create an event in pl/sql and SOA can suybscribe to it. When i was working on that i found that we need to pass XML data as event payload and that we can subscribe from SOA hence i need to come up with this post. We will try to explore the options of creating an XML payload from PL/SQL and then we will use this functionality to subscribe event from SOA


Here is my master data which i will be using to create XML structure



sQL provide some inbuild functionality that we can use to generate an XML document out of rows in a table.

Lets use a query to understand the same.

SELECT XMLElement( "Employee_Name", EmployeeName)
FROM employeedata
WHERE department='Oracle';

This will fetch us the following result. As you can see it fetches all the employee name from the table and displayed against the element Employee_Name

<employee_name>Shreya</Employee_Name>
<employee_name>Swati</Employee_Name>
<employee_name>Arpit</Employee_Name>
<employee_name>Kaushik</Employee_Name>


Now lets try some more complex element out of it. In order to get a complex element of XML we use another function called as XMLForest so the query to fetch more than one elements will be something like this


SELECT XMLForest(EmployeeName as "Employee_Name"
, Department as "EmpDepartment") FROM employeedata;

However this will fetch us result like this

<Employee_Name>Shreya</Employee_Name><EmpDepartment>Oracle</EmpDepartment>
<Employee_Name>Swati</Employee_Name><EmpDepartment>Oracle</EmpDepartment>
<Employee_Name>Arpit</Employee_Name><EmpDepartment>Oracle</EmpDepartment>
<Employee_Name>Kaushik</Employee_Name><EmpDepartment>Oracle</EmpDepartment>

This is ok but we want the whole thing to be wrapped within a root element so we will try to execute a query somwthing like this

SELECT XMLElement("Employee", XMLForest(EmployeeName as "Employee_Name"
, Department as "EmpDepartment"))
FROM employeedata;

Here we are trying to create a root element Employee on top of the results that we have retrieved earlier. So after execution we will have a result like this

<Employee><Employee_Name>Shreya</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee><Employee_Name>Swati</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee><Employee_Name>Arpit</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee><Employee_Name>Kaushik</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>


As you can observe one employee element has department and employee name.Now lets try to add attribute to this. For adding attribute we have another function called as XMLAttributes.

So we will use this fucntion to create another query

SELECT XMLElement("Employee", XMLAttributes( designation as "Designation") , XMLForest(EmployeeName as "Employee_Name"
, Department as "EmpDepartment"))
FROM employeedata;

And this is the result we will get

<Employee Designation="Consultant"><Employee_Name>Shreya</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee Designation="Consultant"><Employee_Name>Swati</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee Designation="Consultant"><Employee_Name>Arpit</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>
<Employee Designation="Consultant"><Employee_Name>Kaushik</Employee_Name><EmpDepartment>Oracle</EmpDepartment></Employee>


Having understood this we will try to use this concept and the concept mentioned in the blog mentioned in this post to create an event and raise an XML payload and subscribe that xml data in soa process