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

parsing xml namespaces to hive

New Contributor

<ns10:AIXMBasicMessage xmlns:ns1="http://www.opengis.net/gml/3.2" xmlns:ns2="http://www.w3.org/1999/xlink" xmlns:ns3="http://www.aixm.aero/schema/5.1" xmlns:ns4="http://www.isotc211.org/2005/gco" xmlns:ns5="http://www.isotc211.org/2005/gmd" xmlns:ns6="http://www.isotc211.org/2005/gts" xmlns:ns7="urn:us.gov.dot.faa.aim.fns" xmlns:ns8="http://www.aixm.aero/schema/5.1/event" xmlns:ns9="http://www.aixm.aero/schema/5.1/extensions/FAA/FNSE" xmlns:ns10="http://www.aixm.aero/schema/5.1/message" 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;
OK
NULL
Time taken: 0.093 seconds, Fetched: 1 row(s)

 

(is what hive is returning) Please help me out