Uploading XML to MySQL with different columns names

[donz365]

I am sorta new to MySQL and not 100% sure of what I can and can not do with it yet. I have an xml file with course information such as ReferenceNo. CourseName, CourseType, CourseLocation, and I want to load in into a database table that has column names such as ID, post_title, term_taxonomy_id etc

I am updating the site by uploading the xml to MySQL (I am using Worpress by the way). The table I am loading it into has different named columns to that of the elements I want to load into it. So for instance I want to load the
ReferenceNo on the xml into the ID column in the database table or the
CourseName on the xml into a column called post_title n the database table

At the moment I am just doing a good ol manual"find and replace" before uploadig it to change the field names to match the column names, but this is not a long term solution. Is there a way to assign the elements of the xml to the db table columns without having to change names to match??

[diafol]

LOAD XML statement and use the SET clause. I'll see if I can dig out an example.

[diafol]

OK here's the PHP/MySQL:

$db = new PDO("mysql:host=localhost;dbname=somedb",'xxxxx','xxxxxxxx', array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));

$query = "LOAD XML LOCAL INFILE 'xml.xml'
INTO TABLE xml ROWS IDENTIFIED BY '<character>'
SET character = role,
    title = film,
    type_of_part = part";

$stmt = $db->prepare($query);
$stmt->execute();

As you can see the file was called xml.xml. Original I know.
Here's the xml I filched from http://www.databasejournal.com/features/mysql/all-about-the-mysql-5.5-load-xml-inline-statement.html

<?xml version=1.0"?>
<character>
    <role>Dirty Harry</role>
    <actor>Clint Eastwood</actor>
    <part>protagonist</part>
    <film>Dirty Harry</film>
</character>
<character>
    <role>Indiana Jones</role>
    <actor>Harrison Ford</actor>
    <part>protagonist</part>
    <film>Raiders of the Lost Ark</film>
</character>
<character>
    <role>Ethan Hunt</role>
    <actor>Tom Cruise</actor>
    <part>protagonist</part>
    <film>Mission Impossible</film>
</character>

You just need to map the fieldnames to the tagnames in the SET clause.

[donz365]

Hi Diafol,

Thanks for your reply. I have applied the SET clause like so:

LOAD XML LOCAL INFILE 'C:/courses.xml' 
INTO TABLE wp_posts ROWS IDENTIFIED BY '<Course>'
SET ID = ReferenceNo,
    post_title = CourseName,
    post_type = 'course';

And I get the following error:

1054 - Unknown column 'ReferenceNo' in 'field list'

This is a snippet of the xml

<?xml version="1.0" encoding="utf-8"?>
    <Course>
    <ReferenceNo>64055</ReferenceNo>
    <ProviderCode>CO</ProviderCode>
    <ProviderName>name deleted</ProviderName>
    <CourseID>CO43Z</CourseID>
    <CourseName>IT Maintenance - Short Course</CourseName>
    <CourseTypeID>1</CourseTypeID>
    <CourseTypeName>1</CourseTypeName>
    <LocationCode>70203</LocationCode>
    <LocationName>name deleted </LocationName>
    <StartDate>2015.12.07</StartDate>
    <FinishDate>2016.03.04</FinishDate>
  </Course>

[diafol]

I think you may find the answer here:

http://stackoverflow.com/questions/29209162/set-clause-error-1054-unknown-column-in-field-list

Posting the same question to multiple forums may be good for you, but ultiimately wastes everybody else's time. In addition it serves to give those forums bad rep with search engines with regard to not hosting original content.

Good luck.

[donz365]

The last thing I am trying to do is "Waste everybodies time", my sincere apologies if this is how I have come across. If anything I am overly-conscious of the time someone offers and do not want to keep badgering them once they have taken the time to help.

I have being reprimanded, lesson learnt thanks for the heads up.

[donz365]

Slight tweak and its working... thanks Diafol :/

LOAD XML LOCAL INFILE 'C:/courses.xml' 
INTO TABLE wp_posts ROWS IDENTIFIED BY '<Course>'
(@ReferenceNo, @ProviderCode, @ProviderName, @CourseID, @CourseName, @LocationCode, @LocationName, @StartDate, @FinishDate)
SET ID = @ReferenceNo,
    post_title = @CourseName,
    post_type = 'course';

[diafol]

Thank you for coming back with the solution, I'm sure that this will help a lot of people. I wasn't aware of the need to "var out" the tagnames. We learn something new every day :)