Hi All,
I am trying to load nested XML file into HIVE table, but i see multiple lines getting generated for the values in Array.
XML File :
<?xml version="1.0" encoding="UTF-8"?>
<FormServerResponse>
<Status>
<StatusCode>0</StatusCode>
<StatusDesc> Success</StatusDesc>
<ConfidenceIndex> D</ConfidenceIndex>
<Messages>
<Message severity="Success">
<StatusCode> 0</StatusCode>
<PageNumber> 1</PageNumber>
<FileName> Filxxxxe.pdf</FileName>
<LayoutFileId> xxxx</LayoutFileId>
<LayoutPageId> xxxxx</LayoutPageId>
<LayoutIdentiferText/>
<Text> xx xxxxxx xx</Text>
</Message>
</Messages>
</Status>
</FormServerResponse>
Command I used to load data into HIVE:
create table ViewTable( StatusCode string,StatusDesc string,ConfidenceIndex string,Messages struct<Message: array<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'
tblproperties
(
"xmlinput.start" = "<Status>"
,"xmlinput.end" = "</Status>"
);
Result :
select * from viewtable;
OK
0 Success D {"message":[{"severity":null,"statuscode":"0","pagenumber":null,"filename":null,"layoutfileid":null,"layoutpageid":null,"layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":"1","filename":null,"layoutfileid":null,"layoutpageid":null,"layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":null,"filename":"Filxxxxe.pdf","layoutfileid":null,"layoutpageid":null,"layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":null,"filename":null,"layoutfileid":"xxxx","layoutpageid":null,"layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":null,"filename":null,"layoutfileid":null,"layoutpageid":"xxxxx","layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":null,"filename":null,"layoutfileid":null,"layoutpageid":null,"layoutidentifertext":null,"text":null},{"severity":null,"statuscode":null,"pagenumber":null,"filename":null,"layoutfileid":null,"layoutpageid":null,"layoutidentifertext":null,"text":"xx xxxxxx xx"}]}
Time taken: 0.423 seconds, Fetched: 1 row(s)
Result expected:
0 Success D {"message":[{"severity":success,"statuscode":"0","pagenumber":1,"filename":Filxxxe.pdf,"layoutfileid":xxxx,"layoutpageid":xxxxx,"layoutidentifertext":null,"text":xx xxxxx xx}]}
Could someone help me where I am making mistake?????
