I have an XML schema having elements to be matched with database table columns. I am using jdbc to find table and column information from a given databse. How do I search tables/columns to dynamically match the element names with corresponding column names in tables. I understand that if it was a select command, one could use soundex function. Thanks in advance. Please help ASAP.
At a high level, because I can't remember this kind of code at the top of my head, But JDBC, as you said gives you the column names in the table. And using the XML parser classes you can get a list of the elements. Since you said that the elements in the XML documents match the field names, why not just use the .equals() method of the String class. Again I am simplifying things and your particular arrangement might not make it possible to do this the easy way. Hope that helps Mark
Thanks for a quick response. Let me restate my requirement. I can get the element name from XML schema. However, I do need to find a matching column name in one of the tables in the database. For example, if the element name (from xml schema) is LastName, I need to find a column of a table with similar names (lname, last_name, etc.) Just like soundex may work in a select or update statement. Any help will be greatly appreciated.
OK, then with that it makes it a bit harder. But if you are using Java1.4 you could use the Regular Expressions classes. Although Lastname - LName might make it tough to come up with just the right expression. Now the best solution is if the database and XML matched exactly. can there be changes to either side? Or do you know this information in advanced. Like you know which tables you want to get the XML data into. If so, then use XSL to transform the XML document to match the data scheme, and then you will have exact matches. If you try to rely on Soundex, and keep the same unknowns and uncertainty, you can never guarantee that the matches you get are correct. Mark
Joined: Mar 07, 2002
Mark, The situation I am facing with gives me only the knowledge of XML schema. The tool (I am working on) is supposed to scavange the client database and find the matching columns from all the tables it can get. Subsequently, it needs to form a query and execute it to get relevant data. Thus, I will have no prior knowledge of tables or their column names. I am using JDK1.4. However, as you rightly pointed out, the regular expression will also have tough time in matching "LastName" to "lname", for example. And, soundex search does not seem to always work properly either. Any other alternative you can think of? Regards, - ramanand
Punt? I'd say you are doomed. The reason is that there are too many variables, and it will never work in all cases. You have to make decisions on what you will allow and won't allow, meaning you cannot say "Work in all cases" because you will fail. Now if you say, LastName and how Soundex works, or taking Last and Name and using Regex is the definition of a field match, then you can get away with that. The only other alternative is to have the user tell which fields match up where. Mark