How to import XML data into mysql table

Question

I tried to load the XML data into Mysql table, I'm getting struggled in loading the xml data into mysql table.

This is my XML file

<entry>
    <id>urn:lsid:ibm.com:blogs:blog-f66c48be-05e9-4ce2-bfae-bcb3f0f0e0d1</id>
    <title type="text">abcd</title>
        <author>
             <name>abcd</name>                  
             <snx:isExternal>false</snx:isExternal>
        </author>
    <published>2019-02-14T21:26:28+05:30</published>
    <updated>2019-02-14T21:26:43+05:30</updated>
    <snx:rank scheme="http://www.example.com/recommendations">0</snx:rank> </entry>

I can't able to get the value inside the snx:rank value.
In my updated tag i have datatime with +05:30 is there any way to remove the value?
I need the name which is nested of author tag, provide me suggestion to get the name

I'm getting error while creating table in mysql

create table tbl_source (id varchar (500),title varchar (500), name varchar(50), updated varchar(50), snx:rank scheme="http://www.example.com/recommendations" int);

without using column name snx:rank column there is no error displayed but I'm getting null in name column.

xml import query

LOAD XML INFILE "/root/CommunityBlog_7cdc528d-7fda-4eb3-bff7-d9ccbc7d8f7b_02202019015559983PM.xml" INTO TABLE tbl_source ROWS IDENTIFIED BY '<entry>';

Nethaji.R · Accepted Answer · 2019-03-04 11:57:21Z

1

create procedure w()
begin declare row_index int;
declare xmlset longtext;
declare row_count int;
set row_index=1;
SET xmlset = LOAD_FILE("path/abc.xml"); 
set row_count  = extractValue(xmlset ,concat('count(','//entry',')'));
while row_index <= row_count do
insert into tbl_source20 (id,updated,name,rank) values(
extractValue(xmlset , concat('/feed/entry[',row_index,']/id')),
extractValue(xmlset , concat('/feed/entry[',row_index,']/updated')),
extractValue(xmlset , concat('/feed/entry[',row_index,']/author/name')),
extractValue(xmlset , concat('/feed/entry[',row_index,']/snx:rank'))
);
set row_index = row_index + 1;
end while;
end

I tried this method to get the data from XML. Then use substring_index function to get the exact data into another table.

answered Mar 4, 2019 at 11:57

Nethaji.R

218 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fifonik Over a year ago

If I understand correctly, you can slightly simplify it: extractValue(xmlset , '/feed/entry[$row_index]/id') & Co

fifonik · Accepted Answer · 2019-02-28 21:57:05Z

0

You can try to deal with some issues you have:

LOAD XML INFILE "/path/to/file.xml" INTO TABLE tbl_source 
ROWS IDENTIFIED BY '<entry>'
(id, title, @author, @updated, @`snx:rank`)
SET
      updated = IF(
              SUBSTR(@updated, 20, 1) = '+'
            , SUBTIME(STR_TO_DATE(LEFT(@updated, 19), '%Y-%m-%dT%H:%i:%s'), SUBSTR(@updated, 21))
            , ADDTIME(STR_TO_DATE(LEFT(@updated, 19), '%Y-%m-%dT%H:%i:%s'), SUBSTR(@updated, 21))
    ) -- converting to UTC
    , rank = @`snx:rank`
;

Unfortunately, I do not know how to deal with the author.

edited Feb 28, 2019 at 21:57

answered Feb 28, 2019 at 21:43

fifonik

1,6461 gold badge11 silver badges20 bronze badges

1 Comment

Nethaji.R Over a year ago

thank you for your reply, unfortunately in my scenario i have two snx:rank lines in my xml file, at the end one is recommendations and another one is view. I need these value in two different column. sorry I forgot to mention that line in question.

Collectives™ on Stack Overflow

How to import XML data into mysql table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related