hive 之 xml 字段解析

1、测试字段

<?xml version="1.0" encoding="UTF-8"?><people><Name>张三</Name><age>20</age><XinXi><XinBie>"男"</XinBie></XinXi></people>
<?xml version="1.0" encoding="UTF-8"?><people><Name>李四</Name><age>22</age><XinXi><XinBie>"男"</XinBie></XinXi></people>
<?xml version="1.0" encoding="UTF-8"?><people><Name>王五</Name><age>21</age><XinXi><XinBie>"男"</XinBie></XinXi></people>

特殊单标签：

<?xml version="1.0" encoding="UTF-8"?><OutputChannel><OnlineInfo><IBStream Level="0"/><IBStream Level="1"/><IBStream Level="2"/><IBStream Level="3"/><IBStream Level="4"/><IBStream Level="5"/></OnlineInfo></OutputChannel>

解析工具查看：

<?xml version="1.0" encoding="UTF-8"?>
<people>
    <Name>张三</Name>
    <age>20</age>
    <XinXi>
        <XinBie>"男"</XinBie>
    </XinXi>
</people>
<?xml version="1.0" encoding="UTF-8"?>
<people>
    <Name>李四</Name>
    <age>22</age>
    <XinXi>
        <XinBie>"男"</XinBie>
    </XinXi>
</people>
<?xml version="1.0" encoding="UTF-8"?>
<people>
    <Name>王五</Name>
    <age>21</age>
    <XinXi>
        <XinBie>"男"</XinBie>
    </XinXi>
</people>

<?xml version="1.0" encoding="UTF-8"?>
<OutputChannel>
    <OnlineInfo>
        <IBStream Level="0"/>
        <IBStream Level="1"/>
        <IBStream Level="2"/>
        <IBStream Level="3"/>
        <IBStream Level="4"/>
        <IBStream Level="5"/>
    </OnlineInfo>
</OutputChannel>

2、HQL解析

使用 xpath_string 函数解析 xml 字段

select xpath_string(ch_xml,'people/Name') ch_xml_Name,
xpath_string(ch_xml,'people/age') ch_xml_age,
xpath_string(ch_xml,'people/XinXi/XinBie') ch_xml_XinBie
from `table_name`;

特殊单标签解析：

select
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[1]/@Level'),
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[2]/@Level'),
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[3]/@Level'),
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[4]/@Level'),
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[5]/@Level'),
xpath_string(ch_xml,'/OutputChannel/OnlineInfo/IBStream[6]/@Level')
from `table_name`;