2

I have a table column that contains XML string. I need to parse the data from each row (XML string column) and insert it into a different table where columns are actually the tags from that XML.

so lets say "tableA.xmlcolumn" contains:

<xml>
    <tag-a>bla bla bla</tag-a>
    <tag-b>bla bla bla</tag-b>
    <tag-c>bla bla bla</tag-c>
</xml>

Into

"tableB"

tag-a       | tag-b       | tag-c
---------------------------------------
bla bla bla | bla bla bla | bla bla bla

TableA have 300K~ rows

How would you approach this using PHP/MySQL?

Thanks!

Ariel Morry
  • 41
  • 1
  • 3
  • You can use `xml_parse_into_struct` to get XML into an associative array and then you can add to your database table. – Shaunak Shukla Jun 19 '17 at 10:33
  • Thanks! actually I was aiming to get if there's a bit more "sophisticated" way than just select > parse in php > insert – Ariel Morry Jun 19 '17 at 10:35
  • "sophisticated" means? Do you want me to write the whole code for you? – Shaunak Shukla Jun 19 '17 at 10:37
  • no. I meant maybe by doing in all using SQL (no php) if possible... or maybe it would be faster/efficient to select all the data and and write a procedure to parse and insert at the same time. I know how to do it, I just think it would take a long time or maybe I'll run out of memory by doing each row with select > php > insert – Ariel Morry Jun 19 '17 at 10:40
  • ohh.. Let me look for that, if there is SQL option to parse XML. – Shaunak Shukla Jun 19 '17 at 10:41
  • I don't believe the examples cited as suitable candidates to answer the question are exact duplicates- they deal with importing xml from a file or string rather than a pure sql solution to reading, processing and inserting xml data and was about to post a suitable answer :( – Professor Abronsius Jun 19 '17 at 11:47

0 Answers0