Avatar of musickmann
musickmann
 asked on

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

I have XML files in a folder. Each file has an associated record in a MySQL table with a field called 'xml'. I need to replace the contents of that field with the contents that are in the file. There is a field in the record that matches the file name of the xml file, so I'm looking for the most straight forward way to do this?

I'm assuming this is not possible with only a SQL statement that reaches out for a file name and uses the contents to update the field, but that would be my preference. Using some external script would be the next step, but that introduces some internal process complexities I'd like to avoid if at all possible.
MySQL ServerDatabases

Avatar of undefined
Last Comment
musickmann

8/22/2022 - Mon
musickmann

ASKER
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?
Peter Chan

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.
meettechie

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Athar Syed

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;

Open in new window

ref: MYSQL 5.7 Documentations - String functions
Mlanda T

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.
musickmann

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Athar Syed

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
musickmann

ASKER
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!