Error: XPATH returns zero node

Problem:
Sometimes you get following runtime error message in your BPEL Console for the transform activity;

XPath query string returns zero node.
According to BPEL4WS spec 1.1 section 14.3, The assign activity part query should not return zero node.
Please check the BPEL source at line number "211" and verify the part xpath query.
Possible reasons behind this problems are: some xml elements/attributes are optional or the xml data is invalid according to XML Schema.
To verify whether XML data received by a process is valid, user can turn on validateXML switch at the domain
administration page.

Solution:
The probable reason for getting this error is due to assigning value to an element using Assign activity just after the Transform activity, for which the same element is not mapped in transformation. Assign activity attempts to update an element not being transformed in Transform (the element without any value).

Following steps should be done to overcome this problem:

1) Goto the transfomation mapper file
2) Right-click on the element that the Assign activity in going to use for assigning value in the next step
3) Select Set Text
4) Set any temporary value there. This will be overwritten by the Assign activity in the next step anyways.

Magikkk of 'Translate'

Ahhh...Writing blog after a longtime. During this time, got an oppurtunity to diagonise BPEL...so now thinking of sharing some of my knowledge and experiences with all.

INTRODUCTION:
Here I am going to discuss a very small function of XSLT:- TRANSLATE.
Translate function returns inputString with occurrences of characters in fromString replaced by the character at the corresponding position in toString. This function takes 3 parameters.

SYNTAX: translate(inputString as string,fromString as string,toString as string)

SCENARIO:
If we deal in currency values, often we see that the value is separated by commas. For example, Value1=2,000,000/Value2=3,000/Value3=30,000

Now suppose you need to store this value in the database. To acheive this you need to remove all the commas from the incoming value. For this purpose we have to use the TRANSLATE function.

SOLUTION:
For the above scenario, use Translate as:
translate(inputString as string,fromString as string,toString as string)

Here,
inputString as string= Your input XPath, for instance, /ns0:Order/ns0:Lines/ns0:UnitPrice/ns0:Money
fromString as string=","
toString as string=""

Now use a conversion function 'Number' to convert the string in number. Thats it and you are done.
Hope I have cleared my point.

Keep learning with me and MERRY CHRISTMAS to all the readers.

XSLT: Remove NameSpaces

This post will guide how to strip the namespaces from qualified XML document. This is most common requirement which comes during working.

Problem: Stripping all 'xmlns' attributes from qualified XML document

Solution:
Here is an XSLT that you can use to strip all namespaces from XML document:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
exclude-result-prefixes="xsl">
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@* | node()"/>
</xsl:element>
</xsl:template>
<xsl:template match="@* | text()">
<xsl:copy/>
</xsl:template>
</xsl:stylesheet>


Learn with me...Way to go...

Using orcl:sequence-next-val

Recently this question was asked to me as a comment to one of my post. I have replied to the comment. I also felt to post this as a blog so that it can prove beneficial to others. Here it goes...

INTRODUCTION:
The function orcl:sequence-next-val takes 2 parameters. It return the next value of an Oracle sequence. It is similar to sequence generation what we see in Oracle SQL.

How to proceed:
First of all you need to drag that function to the tranformation swimlane.
Create a database sequence in Database, say,TEST. This will be the first parameter for the orcl:sequence-next-val function.

The second parameter will be your database connection details, i.e. the database in which your source table is defined.
Suppose your database is having details as:
Username: abhi
Password: abhishek
Host: 172.128.5.13
Port: 1521
SID: Oracle

so the second parameter will look like this:
'jdbc:oracle:thin:abhi/abhishek@172.128.5.13:1521:Oracle'

or for the second parameter you can add a datasource entry in data-sources.xml file under soa_home\j2ee\oc4j_soa\config.

Say the datasource name is BPEL_DBS. so the function will look like,
orcl:sequence-next-val('TEST','jdbc/BPEL_DBS')

After doing this, map it to the destination field.

Your are done...Happy Learnings...

Using "orcl:query-database" in transformation

In this blog, I am going to show the way to tackle dynamic 'where' condition in the function orcl:query-database. It returns a node-set by executing the SQL query against the specified database.

Just a brief introduction about how this function works:-
Signature:
orcl:query-database(sqlquery as string, rowset as boolean, row as boolean, datasource as string)

Arguments:
sqlquery – The SQL query to perform.
rowset – Indicates if the rows should be enclosed in a <rowset> element.
row – Indicates if each row should be enclosed in a <row> element.
datasource – Either a JDBC connect string (jdbc:oracle:thin:username/password@host:port:sid) or a JNDI name for the database.

Example1:
orcl:query-database("select empno from emp
where empno='50'",false(),false(),"jdbc/DbConnection")

Now coming back to the main agenda about giving dynamic value to the where clause. Its pretty simple. Just a little bit trick.

Use the function with the concat function, as:
Example2:
orcl:query-database(concat("select empno from emp where empno=",/tns:Root-Element/tns:Data/:tns:Empno),false(),false(),"jdbc/DbConnection")

Bingooooooooo....

Happppy Learning....

Playing with XSLT- Part 2

Hmmm..Again I am back with some more XSLT stuff.

Problem:
Importing some data from a .csv file(containing date as one field) and inserting it into the database.
If the date field (from csv file) is passed in the format 'YYYY-MM-DD', it works fine. But the problem comes when you try to pass the date value in format 'DD-MM-YYYY' or in any other formats.

Solution:
To explain the above case I have taken the format 'DD-MM-YYYY' as subject.
Weel well..so to acheive it you need to do is to create some embedded java code or use the following in the assign or transformation activity:(subsitute the tns:date with your variable)

concat(substring(tns:Date,orcl:last-index-within-string(tns:Date,"-") + 2.0,4.0),"-",substring(tns:Date,orcl:index-within-string(tns:Date,"-")+2,2.0),"-",substring-before(tns:Date,"-"))


Bingo.....

Playing with XSLT: Convert string to dateTime

Seems that most of us feel difficulty in dealing with date & time in XSLT.
PART-1
Problem: This post tells you about how to map a string source column to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: Test
SourceColumn name: a_str
SourceColumn Value: 20080514
Destination column name: a_dt

Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date, we need to transform the source value. For that we need to use chain of functions.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select='concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0))'/>
</ns2:a_dt>

Bingo...It works.


PART-2
Problem: To map a string source column (containing date and time) to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: TestSource
Column name: a_strSource
Column Value: 20080514111213
Destination column name: a_dt


Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date and Time, we need to transform the source value. For that we need to use chain of functions. Notice that a "T" is concatinated in the code. This one is for adding the Timezone.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select="concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0),'T',substring($d,9.0,2.0),":",substring($d,11.0,2.0),":",substring($d,13.0,2.0))"/>
</ns2:a_dt>

Bingo...It works.

XSLT:Examples of Date and Time Formatting-Part-2

Example: Non-Gregorian Calendars

The above example image is for calendars other than the Gregorian calendar.These examples use non-Latin characters which might not display correctly in all browsers, depending on the system configuration.(Source: w3c xslt doc)

XSLT:Examples of Date and Time Formatting

Example: Gregorian Calendar

The following image above shows a selection of dates and times and the way they might be formatted (Source: w3c transformation doc)