create  table test_db.ez( 
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>>> ,
ProcessSummary struct < TransactionID:string, ProductVersion:string, RequestDate:string, ProcessingTime:string ,FileSize:string , ExtractionTechnique:string >,
ProcessDetails struct < LOBCd:string , FilesReceivedCnt:string, PagesReceivedCnt:string, PagesProcessedCnt:string ,FormsProcessedCnt:string,
              Forms:array<struct<Form:struct<FormName:string , TotalFields:string, FieldsWithData:string, RateBearingFields:string, RateBearingFieldsWithData:string >>>>,
ReturnPayLoad array<struct <ACORD:struct<SignonRq:struct< SessKey:string , ClientDt:string, CustLangPref:string,
                          ClientApp:struct<Name:string, Org:string, Version:string> >>>>


) 
row format serde 'com.ibm.spss.hive.serde2.xml.XmlSerDe' 
with serdeproperties ( 
"column.xpath.StatusCode" = "/FormServerResponse/Status/StatusCode/text()"
,"column.xpath.StatusDesc" = "/FormServerResponse/Status/StatusDesc/text()"
,"column.xpath.ConfidenceIndex" = "/FormServerResponse/Status/ConfidenceIndex/text()"
,"column.xpath.Messages" = "/FormServerResponse/Status/Messages/Message" 
,"column.xpath.ProcessSummary" = " /FormServerResponse/ProcessSummary/*"
,"column.xpath.ProcessDetails" = "/FormServerResponse/ProcessDetails/*"
,"column.xpath.ReturnPayLoad" = "/FormServerResponse/ReturnPayLoad/ACORD/SignonRq"
)
stored as inputformat 'com.ibm.spss.hive.serde2.xml.XmlInputFormat' 
outputformat 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' 
location '/apps/hive/warehouse/Looped'
tblproperties ( "xmlinput.start" = "<FormServerResponse>" ,"xmlinput.end" = "</FormServerResponse>" );