Loading xml data into hive table

Technical Discussions Hive
#1

created a table

create table censusxml (str string);

loaded the below data into this table censusxml;

<response>
<row>
<row _id="228" _uuid="A47815EC-0DA0-499D-9CE6-616A23C4F155" _position="228" _address="https://data.lacity.org/resource/nxs9-385f/228">
<zip_code>91371</zip_code>
<total_population>1</total_population>
<median_age>73.5</median_age>
<total_males>0</total_males>
<total_females>1</total_females>
<total_households>1</total_households>
<average_household_size>1.00</average_household_size>
</row>
<row _id="1" _uuid="1AE8EAF5-D6E6-4AF9-88FD-3CF179F2A8FC" _position="1" _address="https://data.lacity.org/resource/nxs9-385f/1">
<zip_code>90001</zip_code>
<total_population>57110</total_population>
<median_age>26.6</median_age>
<total_males>28468</total_males>
<total_females>28642</total_females>
<total_households>12971</total_households>
<average_household_size>4.40</average_household_size>
</row>
<row _id="318" _uuid="4575FB1E-1A1D-4050-AD91-19FB9D78D279" _position="318" _address="https://data.lacity.org/resource/nxs9-385f/318">
<zip_code>93563</zip_code>
<total_population>388</total_population>
<median_age>44.5</median_age>
<total_males>263</total_males>
<total_females>125</total_females>
<total_households>103</total_households>
<average_household_size>2.53</average_household_size>
</row>
</row>
</response>

when I run this query it shows NULL values: `

select xpath_string (str, ‘/response/row/row/zip_code’) from censusxml;

Whats wrong with the query for this data ? Can someone kindly help?

#2

Hi @sammsundar4905

I think the table is not created as a xml serde,

if you want to pull the data of particular string from the hive table you can use the below query

select * from censusxml where str rlike ‘.(zip_code).’;

Thanks,
shanmukh

1 Like