Support Questions
Find answers, ask questions, and share your expertise

parsing xml namespaces to hive

New Contributor

<ns10:AIXMBasicMessage xmlns:ns1="" xmlns:ns2="" xmlns:ns3="" xmlns:ns4="" xmlns:ns5="" xmlns:ns6="" xmlns:ns7="" xmlns:ns8="" xmlns:ns9="" xmlns:ns10="" ns1:id="FNS_ID_51958424"></ns10:AIXMBasicMessage>




i have to parse this websites and store in table 

i am using xpath()

create table testxml(col1 string, col2 int);



load data local inpath 'testing/xmlsamp.xml' into table testxml;



select * from testxml;




create table testxml1(namespace string);



insert overwrite table testxml1 select xpath_string(col1, 'ns10:AIXMBasicMessage @xmlns:ns1', 'ns10:AIXMBasicMessage @xmlns:ns2', 'ns10:AIXMBasicMessage @xmlns:ns3', 'ns10:AIXMBasicMessage @xmlns:ns4', 'ns10:AIXMBasicMessage @xmlns:ns5', 'ns10:AIXMBasicMessage @xmlns:ns6', 'ns10:AIXMBasicMessage @xmlns:ns7', 'ns10:AIXMBasicMessage @xmlns:ns8', 'ns10:AIXMBasicMessage @xmlns:ns9', 'ns10:AIXMBasicMessage @xmlns:ns10','ns10:AIXMBasicMessage @ns1:id')from testxml;




select * from testxml1;
Time taken: 0.093 seconds, Fetched: 1 row(s)


(is what hive is returning) Please help me out