XML DB Schema and MySQL Project 1 Use the Photo XML below ab

XML DB Schema and MySQL Project

1. Use the Photo XML (below) abd manually create a DB scheme for MySQL for it.

2. Generate an INSERT statement that puts the data in this XML file into the DB.

3. Do that in one file \"ThisWorks.sql\".

4. Test it with XAMPP if needed

<?xml version=\"1.0\" encoding=\"UTF-8\"?>

<photo>

    <creator>John Doe</creator>

    <mimetype>image/jpeg</mimetype>

<location>https://en.wikipedia.org/wiki/Gray_wolf#/media/File:European_grey_wolf_in_Prague_zoo.jpg</location>

    <width>441</width>

    <height>485</height>

    <keywords>

        <keyword>Red</keyword>

        <keyword>Blue</keyword>

        <keyword>Green</keyword>

    </keywords>

</photo>

Solution

CREATE TABLE IF NOT EXISTS `photo` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`creator` varchar(200) NOT NULL,
`mimetype` varchar(200) NOT NULL,
`location` varchar(200) NOT NULL,
`width` varchar(200) NOT NULL,
`height` varchar(200) NOT NULL,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

mysqldump --xml chegg photo > xml_file.xml -u root -p