topic XMLInput to XML column in MSSQLSERVER Database in Talend Studio

XMLInput to XML column in MSSQLSERVER Database

Anonymous — Sat, 16 Nov 2024 14:28:08 GMT

I generate XML files from rows in an Excel input file. Each row of XML formed is put into an XML file each.
I want to load an xml column in my sql server database with each of these xml rows formed in each of the files. How can I do this?
I do not want to load each schema column value in a field in the database. I need to load the whole XML row in each XML file formed into a single XML field in the database table.
I get an "schema from the input link is different from the schema defined in the component" because I have all the schema columns as input from xml but have only a single field to load the whole xml file in the database?
How can I do this? Do I have to use a tMap to create an xml string again and then load it into the database field or is there something else I am supposed to do here.
Any help is appreciated.
Attached is the flow I was trying to do. The flow starting with tfileInputExcel is the one I am having trouble with.

Re: XMLInput to XML column in MSSQLSERVER Database

Anonymous — Thu, 31 Jan 2008 02:32:57 GMT

I get an "schema from the input link is different from the schema defined in the component" because I have all the schema columns as input from xml but have only a single field to load the whole xml file in the database?

After you generate the xml file, use a thenRun connector to load this file into database. Please read this topic to learn how to insert a file into DB.
https://community.talend.com/t5/Design-and-Development/resolved-File-into-BLOB/td-p/67476
Best regards
shong

Re: XMLInput to XML column in MSSQLSERVER Database

Anonymous — Thu, 31 Jan 2008 11:58:09 GMT

I followed the post which you had pointed to, but got the error:
"java.sql.SQLException: Unable to convert between java.io.File and JAVA_OBJECT."
I tried using the same routine given in the post which was how the problem was resolved there,
but my requirement was a bit different ( I do not want to store the whole file in the XML column as is, I only want to store
the XML row/string/ content of the XML) because of which i wrote this routine and resolved the issue.
public static String getFiletoInsert(String file) {
try {
FileReader fileReader = new FileReader(file);
StringBuffer fileDataBuff = new StringBuffer();
String fileData = null;
BufferedReader bufferedReader = new BufferedReader(fileReader);
int i = 0;
while ((fileData = bufferedReader.readLine()) != null) {
if (i != 0) {
fileDataBuff.append(fileData);
}
i++;
}
fileReader.close();
bufferedReader.close();
return fileDataBuff.toString();
} catch (Exception err) {
err.printStackTrace();
return null;
}
}

The expression used in the tMap was :
ByteArrayFromFile.getFiletoInsert(((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")))
for the XML field and the Type of the output column (XML field in the database) was specified as an 'String' and not as an Object in this case.
Thanks for the pointers given to resolve.