[RESOLVED] XML to mySQL

kingmo

Hi,
I have an XML file like this:


<TAGS>
<Item>
<Field Name="Filename">AAABBBCCC.yy</Field>
<Field Name="Category">Download</Field>
<Field Name="Date">2011</Field>
<Field Name="Comment">notes</Field>
</Item>
<Item>
<Field Name="Filename">XXXXTTT.yy</Field>
<Field Name="Date">2011</Field>
<Field Name="font">arial</Field>
</Item>
...
</TAGS>

Between the tags <Item></Item> there isn't the same rows.
I try to import into mySQL with a function simpleXMLToArray descript here, but without significant results.
The problem is the different rows with <item>!
What do i do?
Thank you!

dalecosp

You write your own solution, or you buy one. Altova MapForce, for example, is supposed to do this sort of thing. But, having used it, I'm not even sure it would do a perfect job of mapping disparate fields.

Sounds kind of like your script would need to check whether or not a field exists in the database that matches, and, if not, call "alter table foo add bar (baz) after foolasttable" ...

johanafm

You might also implement it as a two table solution, items and fields, where item holds all information that has to be there which going by your sample data might be (item_id, filename, date) and fields has (item_id, name, value), where name will hold XML Field name attribute values and value will hold XML Field node values.

kingmo

Thank you.
I have another problem: in the first table i don't know filename and date because frequently the XML file change (the users can add other new fields).

If you want HERE there is an example of my XML.

johanafm

First off, I'd recommend not using "name" attributes and a generic field tag. If data is name, then use a name tag. If data is something else, use something-else tag.

<Item>
  <Filename>\Down (no attack).txt</Filename>
  <Content>Note</Content>
  <Name>Up</Name>
  <FileType>txt</FileType>
  <class>Other</class>
  <position>412</position>
  <Recover>HDe</Recover>
  <FileSize>102252</FileSize>
  <Duration>215</Duration>
  <Dates>
	  <Created>1208087626</Created>
	  <Modified>1222334026</Modified>
	  <Imported>1222333287</Imported>
  </Dates>
</Item>

Personally I'd continue to flatten the structure by dropping the <Dates> opening and closing tags and just keep Created, Modified and Imported directly under Item.

Also note that some data might contain invalid characters, most notably in tags with string data such as name. You will need to escape such data or by wrapping it in CDATA tags.

You also have, in several places, nonsensical date data such as

<Field Name="Date">3718</Field>

which should have been rejected on input. If it's supposed to be a date, it should be a valid date.

On a slightly related topic,

  <Created>1208087626</Created>
  <Modified>1222334026</Modified>
  <Imported>1222333287</Imported>

aren't dates, but timestamps. While you may store things as timestamps if you wish, users should not be presented with them. They should see actual dates, times or datetimes.

Input validation should of course be done everywhere. I'll let this one speak for itself except for noting that it's Length, not Lenght. Spelling errors will lead to problems down the road.

<Field Name="Lenght">Hotel</Field>

And since you seem to be dealing with data relating to files, I'd include all of the above in the same table, using a straightforward approach of calling the table columns what they are, rather than putting things in some other table along the field, name, value approach. That is

CREATE TABLE files (
	id			INT UNSIGNED AUTO_INCREMEMT PRIMARY KEY,
	filename	VARCHAR(50),
	name		VARCHAR(50),
	-- if you want to store as timestamps
	created		INT UNSIGNED,
	-- else store it directly as a datetime
	modified	DATETIME,
	-- but do this consistently! go with one or the other, not both...
	imported	DATETIME,
	-- and so on
)

This table really should hold everything which HAS to be there for each file. Some people claim a schema is not normalized if it has table containing null values, but on the other hand Codd claims that null values must be supported in a fully relational DBMS for representing missing information. And in this case, I agree. If a user doesn't supply a name, you could either store null for name, or use the provided filename for both filename and name. That data would not be redundant since they represent different things, just like the same timestamps or dates for created and modified isn't redundant information. Other things might not be possible to fill in under some circumstances, such as file type. If the user doen't provided it and there is no file extension to use, you'd simply leave it as null.

But, certain things are dependent on the file type, such as "duration" which makes sense for video and music, but not for text or images. Images on the other hand might have width and height attributes. Since such metadata is not relevant for all tuples, it could be argued that it should go in a separate table, with one possible solution being additionalMetaData(file_id, name, value) and a primary key of either (file_id, name) or (file_id, name, value).

But you could also opt to keep even duration, widht, height in the original file table and only fall back to the additional table for even more rare cases of meta data. Either way, this is where you'd fall back to the <Field name="stuff">Value goes here</Field> approach.

Things like duration, widht and height could also be handled programatically, even though it does put limits on searchability, if such is to be provided. If a file is of some image type, you could use [man]getimagesize[/man] to find out its dimensions, which means this data does not have to be stored in the database. But if users are supposed to be able to search on dimension, or duration for that matter, such meta data must be stored to avoid this becoming a bottleneck.

kingmo

Thank you johanafm!
I wiil check every data before put in SQL.
But the trouble, for me, is to put my example in mySQL.
I think to work wit two table:
in the first table i will insert all data from XML with a query like this (in a loop to have last data)

"INSERT INTO tbl_one (Field_Name, Field_Date, rowgroup) VALUES ('".$field_name."', '".$field_value."', ".$u.")";

In a second table i want to insert all FIELD and relative data.
For this use this query

$query = "SELECT DISTINCT Field_Name FROM tbl_one"
...

And create this table from the array (created by the query)

Now i have an array like this

Array ( [0] => Array ( [ID1] => 1 [Field_Name] => Filename [Field_Date] => Toots.txt [rowgroup] => 0 ) [1] => Array ( [ID1] => 2 [Field_Name] => Content [Field_Date] => Memories [rowgroup] => 0 ) [2] => Array ( [ID1] => 3 [Field_Name] => Lenght [Field_Date] => 254 [rowgroup] => 0 ) [3] => Array ...

My question is: how do i do to put this multidimensional array in mysql?
Here i have the columns (Filename,Content,Lenght,...) and the VALUE (Toots.txt,Memories,254,...)

johanafm

insert into item
$last_id = mysqli::$insert_id
mysqli::prepare(insert into field(last_id, name, value) value($last_id, :name, :value)
loop over your array and for each set of data create an assoc array
array('name' => $the_name, 'value' => $the_value)
and use that as function parameter in the call to mysqli_STMT::execute()

kingmo

Thank you johanafm!
I used a solution slightly different from the one suggested by you.
My solution is with two tables (as you have suggested).
In first table i'd imported all data of my XML file:


| ID1        | smallint(10)
| Field_Name | varchar(255)
| Field_Data | blob 
| grp        | smallint(10

In Filed_Name and Field_Data insert all Field names and values from XML.
In the second table select (from table one) all fieldname as columns (header) and all Field_Data (value) as rows.

Thanks to everyone who helped me to solve this! 😃

johanafm

This is not "slightly different" from what I suggested. It's something entirely different from what I suggested.

First off, you now have redundant data which you should never have, unless it's used to reference (part of) a primary key, and otherwise break database normalization.

Secondly, why are you storing anything as a blob, unless it's something like an image (which should rather be stored in the filesystem anyway).

Thirdly, you are apparently storing all fieldnames in one column, which means you have compound values stored here, which breaks database normalization.

What I said was, create one table that stores each file and its "core data", such as

-- this stores each file with relevant information which exists for each file
-- or is highly likely to exist for each file
CREATE TABLE file (
	-- 1 .. MAX_INT
	id			INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	-- path/to/filename
	filename	VARCHAR(50),
	-- name given by user, or resort to using filename if no name is supplied
	name		VARCHAR(50),
-- In case you want to store mime type info, you can do it with type, subtype
	-- image,		video,				audio
	type		VARCHAR(15),
	-- jpeg, gif	mpeg, mp4, ogg		mpeg, mp4, vnd.wav
	subtype		VARCHAR(15),
-- or if you just want to store type info in a simpler matter
-- go with this INSTEAD of the above two
	-- mp3, jpg, gif, avi, txt etc
	type		VARCHAR(6)
	-- filesize in bytes, KB or whatever you prefer
	size	INT UNSIGNED,
	-- file creation data
	created DATETIME,
	-- modification date
	modified DATETIME
	-- possibly followed by some other fields
);


CREATE TABLE file_additional (
	file_id		INT UNSIGNED,
	-- any of the xml tag names that are NOT stored in the core table
	name		VARCHAR(50),
	-- and their respective values go here
	value		VARCHAR(50),
	PRIMARY KEY(file_id, name),
	FOREIGN KEY (file_id) REFERENCES file(id)
);

Which means that each file, i.e. each row in table file has 0, 1 or many rows (one per additional xml element which isn't stored in the file table) in table file_additional. But do note that you do not store several tag names in "name" and their values in "value" in the same row. Each and every single one of them goes into its own row.