Strip your XML code for empty nodes

You all must be working with big big xml documents in our complex integration process. Specially if you are using AIA then you will come across huge payload structure. Sometimes i think AIA metadata are a totally waste but that is my views. We often come across situation where we are using huge meta data from AIA and then we just need to pass only few element out of it. In those cases it becomes totally redundant to send all those empty nodes which we are not using.

This will not only create a burden on the BPEL engine to process such a huge payload but also it will make the reading of data unclear to the administrator. So it is always a best practice to truncate/strip your xml data so that all the empty nodes can be removed.

In this exercise we will see how we can strip our xml code.

the magic will be done using the following piece of code

<xsl:output method="xml" indent="yes"/>
<xsl:template match="@*|node()">
<xsl:if test=". != '' or ./@* != ''">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:if>
</xsl:template>



Further there are some other version of the same code which can be used to strip the XML code

<?xml version = '1.0'?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >
<xsl:output method="xml" indent="yes"/>
<xsl:template match="@*|node()">
<xsl:if test=". != '' or ./@* != ''">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:if>
</xsl:template>
</xsl:stylesheet>


<?xml version = '1.0'?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes" />
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="@*[.='']"/>
<xsl:template match="*[not(node())]"/>
</xsl:stylesheet>


You may use any one of them if one of them doesn't give you the desired output.

Now lets see an example of how this can be used to truncate the empty tags from xml element.


I will come up with a very bad example but that will clear your doubt on how to use this code.

I have this weird schema input where in we have to pass 9 combination of user/pwd. In ideal case only one of the user/pwd will be required so rest of them are redundant so in this exercise we will how we can remove the redundant element..

Just to give you an idea this is how my schema for input will look like

<element name="process">
<complexType>
<sequence>
<element name="Username" type="string"/>
<element name="Password" type="string"/>
<element name="Username1" type="string"/>
<element name="Password1" type="string"/>
<element name="Username2" type="string"/>
<element name="Password2" type="string"/>
<element name="Username3" type="string"/>
<element name="Password3" type="string"/>
<element name="Username4" type="string"/>
<element name="Password4" type="string"/>
<element name="Username5" type="string"/>
<element name="Password5" type="string"/>
<element name="Username6" type="string"/>
<element name="Password6" type="string"/>
<element name="Username7" type="string"/>
<element name="Password7" type="string"/>
<element name="Username8" type="string"/>
<element name="Password8" type="string"/>
</sequence>
</complexType>
</element>



Now let me try to use this to truncate the empty element. the assumption here is that the actual usename/pwd will come in the first two elements.

Lets first create a BPEL process out of this schema as an input parameter.

Now create variable of the same type as that of the input element




Create a transformation between the input element and the new variable that you have created.



In the transformation thus create copy and paste the code that we have used earier




Now just test your instance and you will get following results

The input variable that you passed has all the empty tag but after transformation it has only two fields

Subscribe Business Event Using Oracle Apps adapter

In this exercise we will create a simple BPEL process that will subscribe to the event raised in Oracle EBS. The event will be a custom event and we will be sending a xml payload along with the event.There are few very important points that you need to take care of when creating an apps adpater.We will see all of them. First of all it is very important to fix the structure of XML document that you wanted to subscribe from the Apps event. So lets create a simple XML schema (Employee.xsd)which our process will use to subscribe xml from apps event.

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org" targetNamespace="http://www.example.org"
elementFormDefault="qualified">
<xsd:element name="Employee">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpName" type="xsd:string"/>
<xsd:element name="EmpId" type="xsd:integer"/>
<xsd:element name="EmpSalary" type="xsd:integer"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


Now once you have the schema document.

Create a SOA empty composite and paste the schema is the XSD folder of the composite.

Now we will create a event in Oracle apps so that our BPEL process can initiate from that point.

Log in to Oracle Administration Workflow page

And create a new business Event



Now in your composite drag and drop an apps adapter in the Exposed services swimlane and configure it to subscribe to the created business event in oracle apps



Give it some logical name and say next



Point it to the ebs instance where you have created your event





Search for your business event





Now in the next step, switch it to specific schema and point it to the schema you have already copied as shown below



Say next and finish your wizard



Now just drag and drop a BPEL process in composite lane and link it with the apps adpater. Create an assign activity to map the event data to one of the string variable you have created.



Now most of the part is done. Now in order to trigger this process you need to raise an event.YOu can raise an event in two ways one is from the front end screen of Workflow and other is from Pl/SQL. We will first see how we can raise the event from workflow page.

Go to workflow page and search for your business event.

Once you get the event click on test icon as shown below



Now is the important point when testing the event there are some important points that needs to be taken care of

1> The event key is a unique parameter and everytime you test your event you have to pass a unique value to it.

2> The event data will contain the XML content. The XML content must be namespace qualifier otherwise your BPEL process will go to an infinite loop and keep polling and erroring out. Hence it is very important that the xml payload should be namespace qualified with the same namespace of which the schema is made up of.

For our exercise i have already created a sample XML file.

<xsd:Employee xmlns:xsd="http://www.example.org">
<xsd:EmpName>Arpit</xsd:EmpName>
<xsd:EmpId>2224</xsd:EmpId>
<xsd:EmpSalary>450000</xsd:EmpSalary>
</xsd:Employee>

As you can see it contains the same namespace of which the schema is made up of, we will pass this as xml content.

So overall the input will be something like this



There is one more important point that is you have to raise the event in pl/sql as highlighted

Once you raise an event you can see a row getting created in the WF_BPEL_QTAB table in the EBS instance



You can validate if the message is processed or not by again checking another view called as AQ$WF_BPEL_QTAB. The MSG_STATE will tell you whether the message is processed by SOA or not.



A ready state means it is not yet processed and there is some issue. For me the issue was that i have not yet deployed the process to soa, I deployed the soa process and as soon as an instance got created the status changed from ready to processed.

The other way to raise an event which is normally used in integraiton scenario is to raise event throuhg pl/sql.

Take an example you want that as soon as the ship order is delivered the information should be passed on to the legacy system from Oracle EBS. So you can have a trigger on the status of shipment delivery and as soon as it is delivered you can raise the custom event which will pass the payload whatever you want to pass.

In our case we will just create a simple table and try to retrieve data and pass it as the xml format we have designed earlier.

Lets first see the table and then see how we are passing the xml element from this table.

we are using an employeedata table which has following values



Then i am using the following pl/sql code to raise the event with the same payload.

SET SERVEROUTPUT ON
declare
L_EVENT_NAME varchar2 (100) := 'TestEvent';
L_EVENT_KEY varchar2 (100) := 'A23412test1';
L_EVENT_DATA CLOB;

begin
/* Raise the event */

DBMS_OUTPUT.put_line ('event qry start');
select xmlserialize(content (XMLElement("xsd:Employee",XMLATTRIBUTES( 'http://www.example.org' AS "xmlns:xsd"),
XMLForest(EmployeeName as "xsd:EmpName",Department as "xsd:EmpId",EmployeeSalary as "xsd:EmpSalary")))) INTO L_EVENT_DATA FROM employeedata;
DBMS_OUTPUT.put_line ('captured in L_EVENT_DATA');
wf_event.RAISE
(P_EVENT_NAME => L_EVENT_NAME,
P_EVENT_KEY => L_EVENT_KEY,
p_event_data => L_EVENT_DATA
);
DBMS_OUTPUT.put_line ('event raised and commited'||L_EVENT_DATA);
END;
/

Since server output is on you can see the output generated from the query.As you can observe it is namespace qualified and has the exact structure we were looking for to be passed.

1 rows deleted.
1 rows inserted.
anonymous block completed
event qry start
captured in L_EVENT_DATA
event raised and commited



<xsd:Employee xmlns:xsd="http://www.example.org"><xsd:EmpName>Arpit</xsd:EmpName><xsd:EmpId>Oracle</xsd:EmpId><xsd:EmpSalary>4566666</xsd:EmpSalary></xsd:Employee>


Once again go to your WF_BPEL_WTAB table and observe a new record getting created.

Validate the soa instance and validate if the data is getting parsed correctly.

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

Pass Header variables from SOA to OSB

I had quite a hard time finding the solution for how to pass the headers from SOA to OSB. I did a lot of R& D and don't know what exactly clicked but some how i got it working. I am drafting all the steps that i have done to pass the headers. This will help you to by pass doing a lot of experiment to achieve the functionality.I tried passing headers from SOA by a lot of ways. Such as using properties in invoke activity.Then i enable get All headers in OSB option but some how i was not able to get any header data in the $header context variable. I tried to do logging for $inbound and $header variable but i couldn't get the details passed from SOA



Scenario:

The requirement was to send the file name(custom defined) to the target system. The actual payload to the target system was not having any field to hold the fileName so we proposed to pass the same using headers.

Steps to make it working:

First is to create a variable to type string in your BPEL process.I named it as FileName



Next is create a property variable while calling the partnerlink using invoke activity. Go to the source code of your invoke activity which is calling the OSB service and add a property as shown below



Here fileName is the name of the property variable and its value is coming from the variable that we have created earlier "FileName". Now before the invoke activity you have to make sure that the "FileName" variable has the been mapped with the actual custom name of the file which you wanted to send to the OSB.

Now this will send the data to the OSB as a header section.The important point to note down is that we are passing the values using headers and not SOAP action.This is all the change that you need to do in SOA side.

Now in OSB when you create a proxy service you have to do following things

Select get All headers options in OSB



Next while publishing it to end system , Use transport headers and select Name as fileName and in action select "Pass Header through individually". Save all the changes and complete the wizard.



now test your service you should be able to see the transport header getting passed from SOA to OSB