Search code examples
pythonmysqlxmlshellload-data-infile

LOAD XML INFILE save nested childs as plain

I did my research on the internet and it seems, that LOAD XML INFILE could not save nested childs with same names or simply with different names.

imported XML sample here

But is there any option, which could be used to keep whole content in parent as plaintext? Its not problem for me after that to parse that content line by line.

Please do not tell me I need to parse it with PHP, it fails in case of speed and I have many XMLs I need to load, so terminal is best solution for me.

So if there is for example some kind of shell or python script (in case that its not possible to import it as plain).

Thanks in advance

Solution

  • Thank you all for correcting grammar mistakes, its very useful and you should earn another badge for helping to community.

    Since nobody came up with solution, I did following, which helped me:

    1) create file script.py with this contents

    #!/usr/bin/python3
# coding: utf-8

import os
import sys
import fileinput


replacements = {'<Image>':'', '</Image>':';','  ':'','\n':''}

with open('/var/www/html/XX/data/xml/products.xml') as infile, open('/var/www/html/XXX/data/xml/products_clean.xml', 'w') as outfile:
    for line in infile:
        for src, target in replacements.iteritems():
            line = line.replace(src, target)
        outfile.write(line)

    2) run it through terminal

    python /var/www/html/script.py

    3) then you load XML infile that XML to your mysql as usual, or you can transform that column into json for better use