我们将xml数据作为一个名为xml的字符串列加载到hadoop中。我们正在尝试检索数据级别，并将其规范化或分解为单行进行处理（您知道，就像一个表！）已经尝试过爆炸函数，但没有得到我们想要的。

示例xml

<Reports>
<Report ID="1">
<Locations>
  <Location ID="20001">
    <LocationName>Irvine Animal Shelter</LocationName>
  </Location>   
  <Location ID="20002">
    <LocationName>Irvine City Hall</LocationName>
  </Location>   
  </Locations>
</Report>
<Report ID="2">
<Locations>
  <Location ID="10001">
    <LocationName>California Fish Grill</LocationName>
  </Location>   
  <Location ID="10002">
    <LocationName>Fukada</LocationName>
  </Location>   
  </Locations>
</Report>
</Reports>

查询1

我们正在查询更高级别的report.id，然后查询子级（locations/location）的id和名称。下面给出了所有可能组合的笛卡尔积（在本例中，8行而不是我们希望的4行）

SELECT xpath_int(xml, '/Reports/Report/@ID') AS id, location_id, location_name 
FROM   xmlreports 
LATERAL VIEW explode(xpath(xml, '/Reports/Report/Locations/Location/@ID')) myTable1 AS location_id 
LATERAL VIEW explode(xpath(xml, '/Reports/Report/Locations/Location/LocationName/text()')) myTable2 AS location_name;

查询2

试图分组到一个结构中，然后分解，但这将返回两行和两个数组。

SELECT id, loc.col1, loc.col2
FROM (
SELECT xpath_int(xml, '/Reports/Report/@ID') AS id, 
       array(struct(xpath(xml, '/Reports/Report/Locations/Location/@ID'), xpath(xml,     '/Reports/Report/Locations/Location/LocationName/text()'))) As foo
FROM   xmlreports) x
LATERAL VIEW explode(foo) exploded_table as loc;

结果

1   ["20001","20002"]       ["Irvine Animal Shelter","Irvine City Hall"]
2   ["10001","10002"]       ["California Fish Grill","Irvine Spectrum"]

我们想要的是

1   "20001" "Irvine Animal Shelter"
1   "20002" "Irvine City Hall"
2   "10001" "California Fish Grill"
2   "10002" "Irvine Spectrum"

似乎是一件很平常的事情想做，但找不到任何例子。非常感谢您的帮助。