Hey @Anji Raju! I made a test here, and it seems that you need to change your Messages struct<Message: array<struct< for Messages array<struct<Message:struct< Here's my test: #My sample.xml file <Status>
<StatusCode>0</StatusCode>
<StatusDesc>Success</StatusDesc>
<ConfidenceIndex>D</ConfidenceIndex>
<Messages>
<Message>
<severity></severity>
<statuscode>0</statuscode>
<pagenumber>1</pagenumber>
<filename>Filxxxxe.pdf</filename>
<layoutfileid>xxxx</layoutfileid>
<layoutpageid>xxxxx</layoutpageid>
<layoutidentifertext></layoutidentifertext>
<text>xx xxxxxx xx</text>
</Message>
</Messages>
</Status> #Downloading and adding the jar from ibm.spss
wget http://search.maven.org/remotecontent?filepath=com/ibm/spss/hive/serde2/xml/hivexmlserde/1.0.5.3/hivexmlserde-1.0.5.3.jar
mv remotecontent?filepath=com%2Fibm%2Fspss%2Fhive%2Fserde2%2Fxml%2Fhivexmlserde%2F1.0.5.3%2Fhivexmlserde-1.0.5.3.jar hivexmlserde-1.0.5.3.jar
#Creating the HDFS location for the table and putting the sample.xml above hdfs dfs -mkdir /user/hive/warehouse/hive-xml-test
hdfs dfs -put sample.xml /user/hive/warehouse/hive-xml-test
#Starting the test in Hive
hive> add jar /tmp/hivexmlserde-1.0.5.3.jar;
create external table xmltest(
StatusCode string,
StatusDesc string,
ConfidenceIndex string,
Messages array<struct<Message:struct<
severity:string,
statuscode:string,
pagenumber:string,
filename:string,
layoutfileid:string,
layoutpageid:string,
layoutidentifertext:string,
text:string>>>
)
row format serde 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
with serdeproperties (
"column.xpath.StatusCode" = "/Status/StatusCode/text()"
,"column.xpath.StatusDesc" = "/Status/StatusDesc/text()"
,"column.xpath.ConfidenceIndex" = "/Status/ConfidenceIndex/text()"
, "column.xpath.Messages" = "/Status/Messages/Message" )
stored as inputformat 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
location '/user/hive/warehouse/hive-xml-test'
tblproperties ( "xmlinput.start" = "<Status>" ,"xmlinput.end" = "</Status>" );
hive> select * from xmltest;
OK
0 Success D [{"message":{"severity":null,"statuscode":"0","pagenumber":"1","filename":"Filxxxxe.pdf","layoutfileid":"xxxx","layoutpageid":"xxxxx","layoutidentifertext":null,"text":"xx xxxxxx xx"}}]
Time taken: 0.104 seconds, Fetched: 1 row(s)
Hope this helps!
... View more
