Insert childelement values as json from xml in database

Question

I want to insert childelement values as json from xml in database.

My xml

<Listings>
   <Listing>
      <ParentElement>
         ParentValue
      </ParentElement>

      <ParentElement>
         <ChildElement>
             ChildValue
         </ChildElement>
         <ChildElement>
             ChildValue
         </ChildElement>
     </ParentElement>
   </Listing>
</Listings>

I can get the values in doing loop by

foreach($xml->xpath('//ChildElement/*') as $child) {
    echo $child;
}

How can I insert the values of child in my database as json?

[convert it](https://stackoverflow.com/questions/8830599/php-convert-xml-to-json) and save it i jason filed, but when you wantg to change vaules or want to have complicated selects, better save them in rows and columns — nbk, Nov 04 '20 at 14:09
Right now im saving them in a separate table. So its inserting row in every loop. But i might deal with a lot of xml files and might be hardware demanding to. Im thinking of if i can append the value in the loop and then once finished it will save it as json. Basically i will not change values, i will just import the xml and save it to the database and retrieve it. — Metrocole, Nov 04 '20 at 17:12
you can also use xml import from MySQL, if you don't need json, i dislike xml json and other delimited data in columns, it is always a painn to select and get the answers you need — nbk, Nov 04 '20 at 17:28
You mean manual import? I want it to be automatically as i will be dealing with hundreds of xml. But right now with 1 xml file im testing with 70 records and around 900 childelements its taking me like 10sec. I might deal with multiple xmls thats 35x of that. I will mainly save whats on xml and retrieve whats in the column, i will not edit the data once saved in the db. — Metrocole, Nov 05 '20 at 03:10

score 1 · Answer 1 · answered Dec 17 '20 at 12:52

Most of this time might be spend for database communication. Try using a buffer and save multiple records at once using mass insert statements.

INSERT INTO table_name (field_name, ...) VALUES (value_1_1, ...), (value_2_1, ...), ...

Or convert the XML into a simplified structure (XML or CSV) and use the database import.

LOAD XML INFILE '/path/records.xml' INTO TABLE table_name
LOAD DATA INFILE '/path/records.csv' INTO TABLE table_name

Insert childelement values as json from xml in database

1 Answers1