Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

parsing xml namespaces to hive

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

 

Don't have an account?
Coming from Hortonworks? Activate your account here