Parse XML File using HIVE

In this post , we will learn how to parse XML file using hive.

I am using below xml file for this example.

jmdbks@hadoop:~$ cat test.xml
<test><name>Sumit Kumar</name><properties><age>29</age><sex>male</sex></properties></test>
<test><name>Amit Kumar</name><properties><age>30</age><sex>male</sex></properties></test>
<test><name>Aditya Kumar</name><properties><age>23</age><sex>male</sex></properties></test>
<test><name>Priya Kumar</name><properties><age>24</age><sex>Female</sex></properties></test>
<test><name>Rohan Kumar</name><properties><age>20</age><sex>male</sex></properties></test>
<test><name>Nitish Kumar</name><properties><age>29</age><sex>male</sex></properties></test>
jmdbks@hadoop:~$

Below are the Step by Step Procedure to parse XML file using hive .

Step 1:- Create table with single column.

create table test_xml(col string);

Step 2:-Loading XML file into single column table.

load data local inpath '/home/jmdbks/test.xml' into table test_xml;

Step 3:-Create table(Final_table) with required file that is available in XML file.

create table test_xml2(name string,age string,gender string);

Step 4:- insert data into Final_table using hive xpath_string().

hive> insert overwrite table test_xml2 select xpath_string(col,'test/name'),xpath_string(col,'test/properties/age'),xpath_string(col,'test/properties/sex') from test_xml;

hive> select * from test_xml2 ;
OK
Sumit Kumar     29      male
Amit Kumar      30      male
Aditya Kumar    23      male
Priya Kumar     24      Female
Rohan Kumar     20      male
Nitish Kumar    29      male

This is the way, we can parse xml file using hive xpath_string().