on 05-18-2007 8:09 AM
Hi,
I have to develop an XI interface for the following scenario:
The input is an XML file... Depending on one of the field (divsion) in the XML, i have to fetch records (plants) from an oracle database... These values (plants) have to be mapped to a single field in the target.... the output required is also an XML file... the remaining fields of the target have to be mapped from the intial input XML...
Can someone suggest a solution for the same?
Thanks in advance....
Regards,
Karen.
Hi Karen;
In the XI lookup mapping you will have the source xml and the target xml .
In the select query for the xi lookup you fetch the fields from the database based on the field value from the source xml structure.
you will add the result fetched from the database to teh result list.
If you require anymore information you can discuss it further.
Mudit
Award points if it helps
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Putting ot simply here, you can do this using BPM. Receive the source xml in BPM, then do the Oracle database sync call. now you have another result from the DB. You would use another container variable to hold this data. Now have multi mapping with the participating 2 xml messages and create the target xml. Then send.
Your BPM looks like -
Receve- Transform (if at all you have any mapping for Oracle query) - Send to Oracle (Sync) - Transfom (Multimapping) - Send (final XML).
VJ
The input file (XML) is from a legacy system which is a PLM syetem which sends material data.... this data has to go to an MDM system (o/p needed is also XML file)... but one of the fields (plants to which the material is extended) of the target structure will have multiple values.. these values will be picked from an oracle databse table... the other target field mappings are directly from the source...
Regards
Karen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi;
In this case you will have to use BPM and XI lookup.
As you have to map the target file structure to source file fields as well as fields fetched from Oracle database you will have to do XI lookup in this mapping.
Refer These Blogs
Database lookup in XI
/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi
/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler
/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer
Mudit
Award Points if it helps
Karen,
You have 2 options,
A) Use JDBC look up as shown in this blog ,
/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler
B) Use JDBC adapter and a BPM and make a synchronous Select as shown in this blog,
/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step
Regards
Bhavesh
Hi Bhavesh,
I have written the code as mentioned in the blog.
my UDF code is as follows:
//write your code here
String Query = " ";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;
// Build the Query String
Query = "Select STAGE from I2_ROUTEMASTER where ROUTEID = '" + RouteId[0] + "' ";
try{
//Determine a channel, as created in the Configuration
channel = LookupService.getChannel("BS_I2APPG","JDBC_I2SiteMaster_APPG");
//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.
accessor = LookupService.getDataBaseAccessor(channel);
//Execute Query and get the values in resultset
resultSet = accessor.execute(Query);
for(Iterator rows = resultSet.getRows();rows.hasNext();){
Map rowMap = (Map)rows.next();
result.addValue((String)rowMap.get("STAGE"));
}
}
catch(Exception ex){
result.addValue(ex.getMessage());
}
finally{
try{
if (accessor!=null) accessor.close();
}
catch(Exception e){
result.addValue(e.getMessage());
}
}
I also checked the query. It is returning proper values.
Regards,
Karen Pereira
> The resultset could contain multiple rows and i need
> to put value from all these rows in a single target
> field.
If you mean to say that the oyutput field is 1.. unbounded then yes you need to use the Cache / Queue option that you had selected earlier.
The way ahead to debug would be to follow this blog and check the query being created by XI,
<a href="/people/prasad.illapani/blog/2006/10/25/how-to-check-jdbc-sql-query-syntax-and-verify-the-query-results-inside-a-user-defined-function-of-the-lookup-api">How to check JDBC SQL Query Syntax and verify the query results inside a User Defined Function of the Lookup API</a>
Regards
Bhavesh
can u elaborate ur case....
can u give the XML structure of ur input file...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hey
have a look at this
/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30
/people/arpit.seth/blog/2005/06/27/rfc-scenario-using-bpm--starter-kit
thanx
ahmad
Message was edited by:
Ahmad
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.