creating fields for loading xml to mysql

Question

im trying to upload a xml data feed to mysql so i can use the database with my php program. The problem that im having is usually i create the fields and import the feed. But the feed i have now is a bit strange..

<name>test</name>
<address>test address</address>
<phone>251447</phone>
</phone_count>
<pets>cat</pets>
</pet_count>
<images>
  <image>1.jpg</image>
  <image>2.jpg</image>
  <image>4.jpg</image>
  <image>16.jpg</image>
</images>

can someone tell me how can i create the fields for </phone_count> </pet_count> because those fields have only the closing tag.

how do i create a field for each image? because i haven't inserted data in a sub set.

im trying to load the data using

LOAD DATA LOCAL INFILE '/tmp/xml/data.xml'
INTO TABLE person_data

In XML, only </phone_count> or </pet_count> can not be a valid xml tag — Piyas De
– Piyas De, Commented Mar 19, 2013 at 14:42

RandomSeed · Accepted Answer · 2013-03-19 14:48:35Z

1

Your input XML file is broken, there is no such thing as a closing tag without a corresponding opening tag. The author of this XML flow probably meant <phone_count/> and <pet_count/> (notice the position of the forward-slash). This syntax is identical to (e.g.) <pet_count></pet_count>.

Regaring the method for importing this flow, you will not be able to do it with LOAD DATA INFILE because there is a 1-n (one-to-many) relationship between your main entity (I'm guessing something like "Contact") and Images. Ideally, there will be two tables in your database, such as contact and image, and LOAD DATA INFILE allows importing into one table at a time.

You would be better off parsing this file programatically in PHP.

On second thought, if there are at most four images for one givent contact, then I could imagine a contact table with four image1, ..., image4 fields, but I would not consider it good practice.

edited Mar 19, 2013 at 14:48

answered Mar 19, 2013 at 14:42

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LiveEn Over a year ago

Thanks... The problem was with data feed. They hadn't included the correct tags.

Collectives™ on Stack Overflow

creating fields for loading xml to mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related