0

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??

2
Contributors
7
Replies
50
Views
2 Years
Discussion Span
Last Post by diafol
0

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

1

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.

Edited by diafol

1

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>

Edited by happygeek: personal information deleted

Votes + Comments
I'd hit it
0

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.

2

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';
Votes + Comments
Great! Thanks for sharing
1

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 :)

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.