XML to SQL

Create an SQL table based on your XML data. Afterwards import entire XML data to SQL table.

Assuming that the XML data has following entries in a row:

• Id
• Reputation
• CreationDate
• DisplayName
• LastAccessDate
• WebsiteUrl
• Location
• AboutMe
• Views
• UpVotes
• DownVotes
• AccountId

SQL query for creating a table “user” with these columns: Setting sql_mode=’’ is helpful while taking TIMESTAMP data. Assuming that XML data is stored in Users.xml file which is present in the same directory.

SET sql_mode='';

CREATE TABLE users (Id INT NOT NULL PRIMARY KEY, Reputation INT,  CreationDate TIMESTAMP, DisplayName VARCHAR(50), LastAccessDate TIMESTAMP, WebsiteUrl VARCHAR(256), Location VARCHAR(50), AboutMe VARCHAR(256),  Views INT, UpVotes INT, DownVotes INT, AccountId INT);

LOAD XML LOCAL INFILE './Users.xml' INTO TABLE users(Id, Reputation, CreationDate, DisplayName, LastAccessDate, WebsiteUrl, Location, AboutMe, Views, UpVotes, DownVotes, AccountId);

Above code will import Users.xml into a table in SQL Database which can be loaded as a pandas dataframe for further processing and can be saved in any suitable format.