Hello, everybody. This is my first blog, finally. I wanna to get some points, and make some friends. You can also visit my msn space: http://shenpng.spaces.live.com.  I'm a fun of  NetWeaver. As a PI Consultant, I'm also interested in EP,MDM,BI.

Recently, we have hunderds of scenarios like jdbc to proxy, proxy to jdbc. on the project. I'm tired to create so many data types.I hate routine works. Then I found that, I could write a program to generate XSD, and import it for data type definition. first, I collect all  fields in one txt file for each table. 

My program will read all txt files in the folder, and generate XSD files like this:

Now import the XSD one by one for data types. You can get what you want.

Here I can generate data types for jdbc receiver and sender, and proxy, and file adapters.

Finally, I put my code pieces here(with JDK5 runtime). If you have any question please contact me.

package tf.xi.util; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.util.Scanner; public class Test { /** * @param args */ public static void main(String[] args) throws IOException{ Scanner s = null; String fin="d:\\xi\\pitest\\in";// put your source file here. String fout="d:\\xi\\pitest\\out";//xsd here String ns ="http://topfine.com/TG/SD";//edit your namespace as you are working on. ; try { File f=new File(fin); //TODO remove all files in out folder. if (f.isDirectory()){ File[] a =f.listFiles(); for(int i=0;i<a.length;i++){ s = new Scanner(new BufferedReader(new FileReader(a[i]))); String f1=a[i].getName(); f1=f1.substring(0,f1.indexOf("."));//get file name without subfix. String head1="<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +"<xsd:schema targetNamespace=\""+ns+"\" xmlns=\""+ns+"\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">\n"; String g1="<xsd:complexType name=\""+f1+"_snd\">\n" + "<xsd:sequence><xsd:element name=\"STATEMENT\" minOccurs=\"0\">\n" +"<xsd:complexType><xsd:sequence><xsd:element name=\"dbTableName\">\n" +"<xsd:complexType><xsd:sequence><xsd:element name=\"TABLE\" type=\"xsd:string\"></xsd:element>\n" +"<xsd:element name=\"access\" minOccurs=\"0\" maxOccurs=\"unbounded\" ><xsd:complexType><xsd:sequence>\n"; String g2="<xsd:complexType name=\""+f1+"_rcv\">\n" + "<xsd:sequence><xsd:element name=\"row\" minOccurs=\"0\" maxOccurs=\"unbounded\">\n" + "<xsd:complexType><xsd:sequence>\n"; String r1=""; String str=""; while (s.hasNext()) { str=s.next(); // System.out.println(str);.toUpperCase() .toLowerCase() r1=r1+"<xsd:element name=\""+str+"\" type=\"xsd:string\" minOccurs=\"0\"></xsd:element>\n"; } // get file name. String foot1="</xsd:sequence></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name=\"action\" type=\"xsd:string\"></xsd:attribute></xsd:complexType></xsd:element>" + "</xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:schema>"; String foot2="</xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:schema>"; //System.out.println(h1); String out1=fout+"\\"+f1+"_snd.xsd"; String out2=fout+"\\"+f1+"_rcv.xsd"; BufferedWriter output = new BufferedWriter(new FileWriter(out1)); //output1. for receiver JDBC. output.write(head1+g1+r1+foot1); output.close(); //output 2. for JDBC sender and proxy. BufferedWriter output2 = new BufferedWriter(new FileWriter(out2)); output2.write(head1+g2+r1+foot2); output2.close(); System.out.println("-----"+f1+" Generated-------"); } } } finally { if (s != null) { s.close(); System.out.println("----------- Process END---------"); } } } } } 

Note: 1, how to use this tool, for example, Txt file sd002, got sd002_snd.xsd. Then you should define data type sd002_snd, and namespace, which should be you are working on, or you can't import the XSD.

2,Regarding occurrence. My code only handles a general situation, you can edit it as you like.

3,Regarding data type, I use 'string' for  string, date,integer. If your data is in right format ,then 'string' will work fine. You can change it to 'date' or any other type later.

4,Regarding data structure, My code will only work for a general purpose, You can use 'copy','paste','insert' button in IR. Not one-step-for-all, but save lots of effort. 

5, field name uppercase or lowercase. It depends on your database. Use Java method, toUpperCase()  or toLowerCase().