CODEWITHSUNDEEP
mysqlxml
Nethaji.R
Nethaji.R

Reputation: 21

How to import XML data into mysql table

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>
  1. I can't able to get the value inside the snx:rank value.
  2. In my updated tag i have datatime with +05:30 is there any way to remove the value?
  3. 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>';

Upvotes: 1

Views: 138

Answers (2)

Nethaji.R
Nethaji.R

Reputation: 21

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.

Upvotes: 1

fifonik
fifonik

Reputation: 1606

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.

Upvotes: 0

Related Questions