title: Parse XML in Hive
body: <p>I have the below nested xml which I have to parse as table in hive. </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;animals&gt;
      &lt;animals_data key="72" Value="LD3" /&gt;
      &lt;Lions&gt;
          &lt;Lion key="false" Value="abc"&gt;
              &lt;Tigers&gt;
                  &lt;Tiger key="false" Value="1"&gt;
                      &lt;Tiger_details key="6" Value="asdsa" /&gt;
                      &lt;Tiger_details key="7" Value="adas" /&gt;
                      &lt;Tiger_details key="8" Value="adsa" /&gt;
                      &lt;Tiger_details key="9" Value="adas" /&gt;
                      &lt;Cats&gt;
                          &lt;Cat key="false" Value="sds"&gt;
                              &lt;cat_details key="18" Value="jhgj" /&gt;
                              &lt;cat_details key="19" Value="jhjhg" /&gt;
                              &lt;Pubs&gt;
                                  &lt;Pub key="false" Value="1"&gt;
                                      &lt;Pub_details key="24" Value="6" /&gt;
                                      &lt;Pub_details key="25" Value="43" /&gt;
                                      &lt;Dogs&gt;
                                          &lt;D key="false" Value="1"&gt;
                                              &lt;D_d key="35" Value="4" /&gt;
                                              &lt;D_d key="36" Value="4" /&gt;
                                          &lt;/D&gt;&lt;D k="false" v="2"&gt;
                                              &lt;D_d key="35" Value="4" /&gt;
                                              &lt;D_d key="36" Value="5" /&gt;
                                          &lt;/D&gt;
                                      &lt;/Dogs&gt;
                                  &lt;/Pub&gt;
                              &lt;/Pubs&gt;
                          &lt;/Cat&gt;
                          &lt;Cat key="false" Value="lko"&gt;
                              &lt;cat_details key="18" Value="fgf" /&gt;
                              &lt;cat_details key="19" Value="gfgf" /&gt;
                          &lt;Pubs&gt;
                                  &lt;Pub key="false" Value="1"&gt;
                                      &lt;Pub_details key="27" Value="54" /&gt;
                                      &lt;Pub_details key="48" Value="554" /&gt;
                                      &lt;Dogs&gt;
                                          &lt;D key="false" Value="1"&gt;
                                              &lt;D_d key="68" Value="9" /&gt;
                                              &lt;D_d key="69" Value="2" /&gt;
                                          &lt;/D&gt;
                                      &lt;/Dogs&gt;
                                  &lt;/Pub&gt;
                                  &lt;Pub key="false" Value="2"&gt;
                                      &lt;Pub_details key="27" Value="7" /&gt;
                                      &lt;Dogs&gt;
                                          &lt;D key="false" Value="1"&gt;
                                              &lt;D_d key="68" Value="92" /&gt;
                                              &lt;D_d key="69" Value="21" /&gt;
                                          &lt;/D&gt;
                                      &lt;/Dogs&gt;
                                  &lt;/Pub&gt;
                              &lt;/Pubs&gt;
                          &lt;/Cat&gt;
                      &lt;/Cats&gt;
                  &lt;/Tiger&gt;
              &lt;/Tigers&gt;
          &lt;/Lion&gt;
      &lt;/Lions&gt;
  &lt;/animals&gt;
  </code></pre>
  
  <p>I checked the following stackoverflow post <a href="https://stackoverflow.com/questions/41481582/exception-while-using-lateral-view-in-hive">Exception while using lateral view in Hive</a></p>
  
  <p>The xml format in the post differs from what I have. How can I parse this xml using the xml serde mentioned in the said post or using any other method.</p>
  
  <p><strong>EDIT: XPATH QUERY</strong></p>
  
  <pre><code>SELECT 
  xpath(xmls,'/animals/animals_data/@key') AS animals_data_key,
  xpath(xmls,"/animals/animals_data/@Value")  AS animals_data_value,
  xpath(xmls,"/animals/Lions/Lion/@key") AS lion_key,
  xpath(xmls,"/animals/Lions/Lion/@Value") AS lion_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/@key") AS tiger_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/@Value") AS tiger_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Tiger_details/@key") AS tiger_details_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Tiger_details/@Value") AS tiger_details_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/@key") AS cat_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/@Value") AS cat_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/cat_details/@key") as cat_details_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/cat_details/@Value") AS cat_details_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/@key") AS pubs_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/@Value") AS pubs_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Pub_details/@key") AS pub_details_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Pub_details/@Value") AS pub_details_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/@key") AS d_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/@Value") as d_value,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/D_d/@key") AS dd_key,
  xpath(xmls,"/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/D_d/@Value") AS dd_value
  FROM xmldata;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/c89p8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c89p8.png" alt="XPATH Output"></a></p>
  
  <p><strong>Serde Query</strong></p>
  
  <pre><code>add jar adl://aoadls.azuredatalakestore.net/cluster/hadoop/xmlserde/hivexmlserde-1.0.5.3.jar;
  
  DROP TABLE IF exists sample3;
  CREATE EXTERNAL TABLE sample3(
  animals_data_key ARRAY&lt;STRING&gt;,
  animals_data_value ARRAY&lt;STRING&gt;,
  lion_key ARRAY&lt;STRING&gt;,
  lion_value ARRAY&lt;STRING&gt;,
  tiger_key ARRAY&lt;STRING&gt;,
  tiger_value ARRAY&lt;STRING&gt;,
  tiger_details_key ARRAY&lt;STRING&gt;,
  tiger_details_value ARRAY&lt;STRING&gt;,
  cat_key ARRAY&lt;STRING&gt;,
  cat_value ARRAY&lt;STRING&gt;,
  cat_details_key ARRAY&lt;STRING&gt;,
  cat_details_value ARRAY&lt;STRING&gt;,
  pub_key ARRAY&lt;STRING&gt;,
  pub_value ARRAY&lt;STRING&gt;,
  pub_details_key ARRAY&lt;STRING&gt;,
  pub_details_value ARRAY&lt;STRING&gt;,
  d_key ARRAY&lt;STRING&gt;,
  d_value ARRAY&lt;STRING&gt;,
  dd_k ARRAY&lt;STRING&gt;,
  dd_v ARRAY&lt;STRING&gt;)
  ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
  WITH SERDEPROPERTIES (
  "column.xpath.animals_data_key"="/animals/animals_data/@key",
  "column.xpath.animals_data_value"="/animals/animals_data/@Value",
  "column.xpath.lion_key"="/animals/Lions/Lion/@key",
  "column.xpath.lion_value"="/animals/Lions/Lion/@Value",
  "column.xpath.tiger_key"="/animals/Lions/Lion/Tigers/Tiger/@key",
  "column.xpath.tiger_value"="/animals/Lions/Lion/Tigers/Tiger/@Value",
  "column.xpath.tiger_details_key"="/animals/Lions/Lion/Tigers/Tiger/Tiger_details/@key",
  "column.xpath.tiger_details_value"="/animals/Lions/Lion/Tigers/Tiger/Tiger_details/@Value",
  "column.xpath.cat_key"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/@key",
  "column.xpath.cat_value"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/@Value",
  "column.xpath.cat_details_key"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/cat_details/@key",
  "column.xpath.cat_details_value"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/cat_details/@Value",
  "column.xpath.pub_key"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/@key",
  "column.xpath.pub_value"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/@Value",
  "column.xpath.pub_details_key"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Pub_details/@key",
  "column.xpath.pub_details_value"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Pub_details/@Value",
  "column.xpath.d_key"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/@key",
  "column.xpath.d_value"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/@Value",
  "column.xpath.dd_k"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/D_d/@key",
  "column.xpath.dd_v"="/animals/Lions/Lion/Tigers/Tiger/Cats/Cat/Pubs/Pub/Dogs/D/D_d/@Value"
  )
  STORED AS
  INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
  LOCATION '/samplexml/'
  TBLPROPERTIES (
  "xmlinput.start"="&lt;animals&gt;",
  "xmlinput.end"="&lt;/animals&gt;"
  );
  </code></pre>
  
  <p>I have attached the corresponding output as well. How can I expand the output to multiple rows instead of single row.</p>
  
  <p><a href="https://i.stack.imgur.com/xmisc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xmisc.png" alt="SerDe output"></a></p>
  
  <p><strong>Select Query</strong></p>
  
  <pre><code>add jar adl://aoadls.azuredatalakestore.net/cluster/hadoop/xmlserde/hivexmlserde-1.0.5.3.jar;
  
  --SELECT * FROM sample3;
  
  SELECT 
      adk.adkvalue AS animals_data_key,
      adl.adlvalue AS animals_data_value,
      lk.lkvalue AS lion_key,
      lv.lvvalue AS lion_value,
      tk.tkvalue AS tiger_key,
      tv.tvvalue AS tiger_value,
      tdk.tdkvalue AS tiger_details_key,
      tdv.tdvvalue AS tiger_details_value,
      ck.ckvalue AS cat_key,
      cv.cvvalue AS cat_value,
      cdk.cdkvalue AS cat_details_key,
      cdv.cdvvalue AS cat_details_value,
      pk.pkvalue AS pub_key,
      pv.pvvalue AS pub_value,
      pdk.pdkvalue AS pub_details_key
      --pdv.pdvvalue AS pub_details_value
  from sample3
  LATERAL VIEW explode(animals_data_key) adk as adkvalue
  LATERAL VIEW explode(animals_data_value) adl as adlvalue
  LATERAL VIEW explode(lion_key) lk as lkvalue
  LATERAL VIEW explode(lion_value) lv as lvvalue
  LATERAL VIEW explode(tiger_key) tk as tkvalue
  LATERAL VIEW explode(tiger_value) tv as tvvalue
  LATERAL VIEW explode(tiger_details_key) tdk as tdkvalue
  LATERAL VIEW explode(tiger_details_value) tdv as tdvvalue
  LATERAL VIEW explode(cat_key) ck as ckvalue
  LATERAL VIEW explode(cat_value) cv as cvvalue
  LATERAL VIEW explode(cat_details_key) cdk as cdkvalue
  LATERAL VIEW explode(cat_details_value) cdv as cdvvalue
  LATERAL VIEW explode(pub_key) pk as pkvalue
  LATERAL VIEW explode(pub_value) pv as pvvalue
  LATERAL VIEW explode(pub_details_key) pdk as pdkvalue
  </code></pre>
  
  <p><strong>Select Query Output</strong>
  <a href="https://imgur.com/a/r5UKe" rel="nofollow noreferrer">http://imgur.com/a/r5UKe</a></p>