Need to update value in SQL records with contents from external files

I'm wondering if i can use the infile option in MySQL, but I'm not sure how to create a txt file out of a list of xml files?

Hi,
You have to create relevant project (for instance, C# project), to loop through the Xml file, to generate relevant Insert/Update statement, to apply to the database.

Please refer the below link which explains working with XML data in MySQL.

http://www.databasejournal.com/features/mysql/article.php/3846526/Working-with-XML-Data-in-MySQL.htm

Hope this helps you.

Have you tried to use Load_File(filename)

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

Select all Open in new window

ref: MYSQL 5.7 Documentations - String functions

but I'm not sure how to create a txt file out of a list of xml files
You have to loop through each of the files in question. You cannot just merge these files into one (unless there is no document declaration in each xml file, and there is just an xml fragment in there). You can use athar13's LOAD_FILE for each file.

Thank you for the responses, if the LoadFile option requires files to be on the server then I'll have to pass this up the chain as I only have remote access to the MySQL server, not file access to the server.

I guess I should mention the root cause of the question is that a whole host of files were loaded into the DB but in the transition, the character encoding was missing in the chain and now the records have special characters that appear as ?, I'm trying to find the most effective way of replace the bad data with the source files. The DB table and the files are both UTF-8, so I'm guessing something went wrong in the loading of the file.

I can certainly look into creating some program to do the looping through my files and create an update statement for each file. The idea of using the load file and combing xml files was to create the txt file I thought would be required that would look like this:

item_id, xml
12345, [contents of 12345.xml]
12346, [contents of 12346.zml]
etc.

Thanks athar13, sorry for leaving that bit out.

Based on your thoughts there, I'm thinking I won't be able to do this on my end. My limited access would be the issue, but at least now I think I have some info to have a good discussion with the other team that would be responsible for this.

One task they asked of me that I didn't understand, was that they wanted be to take the directory of xml files I have, then create a tab delimited file that had one column with the item ID and then one column contained the xml text (with special characters). I'm not sure of how to do that without writing yet another script, so I think Ive got what I need to move this discussion on to the next level.

Thanks!