title: Loading XML file to Hive tables
body: <p>We are working on loading a semi-structured XML file in Hive tables. These are retail purchase data.<br>
  I have attached a sample XML file to understand how the data looks and also the Hive table definition which i am using to read that XML file.</p>
  
  <p>The file has multiple Payment and Amount(Amt) sections under "Tender" for each Basket in the XML. For example a customer can pay by Cash,EFTPOS,Credit card,Loyalty card or sometimes use a combination of these.</p>
  
  <p>While reading the data from the attached XML , the results show the Payment column merged with entries in the XML and for 'Amt' it shows NULL.</p>
  
  <p>I understood that the table which I am using to read the data is NOT 100% correct.<br>
  Please let me know how i can create multiple records for same element names "Payment" and "Amt"using the xpath functions</p>
  
  <p>I tried to find some documentation in internet for the same and couldn't find anything which is similar to my scenario.</p>
  
  <p>Sample data:</p>
  
  <pre><code>&lt;Bskt&gt;
    &lt;TillNo&gt;4&lt;/TillNo&gt;
    &lt;BsktNo&gt;1747&lt;/BsktNo&gt;
    &lt;DateTime&gt;2017-10-31T10:51:25.000+11:00&lt;/DateTime&gt;
    &lt;OpID&gt;10115&lt;/OpID&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;CSH&lt;/PayType&gt;
      &lt;Amt&gt;46.75&lt;/Amt&gt;
    &lt;/Tender&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;ITMLOY&lt;/PayType&gt;
      &lt;Amt&gt;0&lt;/Amt&gt;
      &lt;CardNo&gt;2679911927&lt;/CardNo&gt;
      &lt;Program&gt;SmartRewards&lt;/Program&gt;
      &lt;Earn&gt;46.00&lt;/Earn&gt;
      &lt;Burn&gt;0.00&lt;/Burn&gt;
    &lt;/Tender&gt;
  &lt;/Bskt&gt;
  &lt;Bskt&gt;
    &lt;TillNo&gt;4&lt;/TillNo&gt;
    &lt;BsktNo&gt;1748&lt;/BsktNo&gt;
    &lt;DateTime&gt;2017-10-31T10:53:11.000+11:00&lt;/DateTime&gt;
    &lt;OpID&gt;10115&lt;/OpID&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;CSH&lt;/PayType&gt;
      &lt;Amt&gt;46.75&lt;/Amt&gt;
    &lt;/Tender&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;ITMLOY&lt;/PayType&gt;
      &lt;Amt&gt;0&lt;/Amt&gt;
      &lt;CardNo&gt;2619183833&lt;/CardNo&gt;
      &lt;Program&gt;SmartRewards&lt;/Program&gt;
      &lt;Earn&gt;46.00&lt;/Earn&gt;
      &lt;Burn&gt;0.00&lt;/Burn&gt;
    &lt;/Tender&gt;
  &lt;/Bskt&gt;
  &lt;Bskt&gt;
    &lt;TillNo&gt;4&lt;/TillNo&gt;
    &lt;BsktNo&gt;1753&lt;/BsktNo&gt;
    &lt;DateTime&gt;2017-10-31T11:19:34.000+11:00&lt;/DateTime&gt;
    &lt;OpID&gt;50056&lt;/OpID&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;CSH&lt;/PayType&gt;
      &lt;Amt&gt;28.10&lt;/Amt&gt;
    &lt;/Tender&gt;
    &lt;Tender&gt;
      &lt;PayType&gt;ITMLOY&lt;/PayType&gt;
      &lt;Amt&gt;0&lt;/Amt&gt;
      &lt;CardNo&gt;8263734549&lt;/CardNo&gt;
      &lt;Program&gt;SmartRewards&lt;/Program&gt;
      &lt;Earn&gt;28.00&lt;/Earn&gt;
      &lt;Burn&gt;0.00&lt;/Burn&gt;
    &lt;/Tender&gt;
  &lt;/Bskt&gt;
  </code></pre>
  
  <p>Hive table:</p>
  
  <pre><code>CREATE EXTERNAL TABLE BASKET_TENDER (
  `DateTime` string,
  `BsktNo` double,
  `TillNo` int,
  `PayType` string,
  `Amt` float,
  `CardNo` string,
  `Program` string,
  `Earn` float,
  `Burn` float
  )
  
  ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
  WITH SERDEPROPERTIES (
  
  "column.xpath.DateTime"="/Bskt/DateTime/text()",
  "column.xpath.BsktNo"="/Bskt/BsktNo/text()",
  "column.xpath.TillNo"="/Bskt/TillNo/text()",
  "column.xpath.PayType"="/Bskt/Tender/Paytype/text()",
  "column.xpath.CardNo"="/Bskt/Tender/CardNo/text()",
  "column.xpath.Amt"="/Bskt/Tender/Amt/text()",
  "column.xpath.Program"="/Bskt/Tender/Program/text()",
  "column.xpath.Earn"="/Bskt/Tender/Earn/text()",
  "column.xpath.Burn"="/Bskt/Tender/Burn/text()"
  )
  
  STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
      OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
      LOCATION '&lt;hdfs file location&gt;'
      TBLPROPERTIES (
      "xmlinput.start"="&lt;Bskt","xmlinput.end"="&lt;/Bskt&gt;"
  );
  </code></pre>
  
  <p>Hive query output:</p>
  
  <pre><code>select * from BASKET_TENDER
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/hq3Im.png" alt="Query result"></p>