MYSQL -解析XML并运行查询

Question

我很难将XML解析成MySQL。将XML解析为csv的另一个选项是不可行的，因为它看起来xmlstarlet在AIX7.1.0.0中不可用。在研究MySql参考手册时，我意识到我正在处理的XML并不是完全支持的。我有四种不同类型的XML文件。让我们以一个为例。

    <MovementReport version="0100">
    <ControlArea>
        <Sender>
            <Division>WCS1</Division>
            <Confirmation>2</Confirmation>
        </Sender>
        <CreationDateTime>2018-04-17T15:39:32Z</CreationDateTime>
        <RefId>
            <Id>6897731</Id>
        </RefId>
    </ControlArea>
    <DataArea>
        <RequestId>080030603</RequestId>
        <FromLocation>
            <MHA>ID1</MHA>
            <Rack></Rack>
            <X></X>
            <Y></Y>
        </FromLocation>
        <StUnit>
            <StUnitId>M1813236    </StUnitId>
        </StUnit>
        <ToLocation>
            <MHA>A</MHA>
            <Rack>011</Rack>
            <X>065</X>
            <Y>019</Y>
        </ToLocation>
        <ReasonCode>00</ReasonCode>
        <StandAloneFlag>W</StandAloneFlag>
        <Information>No info!</Information>
    </DataArea>
</MovementReport>

为了填充一些列，我必须使用ROWS IDENTIFIED BY。我尝试了上面命令中的几乎所有标记，并得到了以下sql

USE xml_lcs; TRUNCATE TEST01;
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<DataArea>'
   SET N_ID='A';
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<ToLocation>'  
   (@MHA, @Rack, @X, @Y)
   SET t_MHA=@MHA, t_Rack=@Rack,t_X=@X, t_Y=@Y;
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<StUnit>'
   SET N_ID='A';
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<FromLocation>'
   (@MHA, @Rack, @X, @Y)
   SET f_MHA=@MHA, f_Rack=@Rack,f_X=@X, f_Y=@Y;
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<Sender>'
   SET N_ID='A';
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<RefId>'
   SET N_ID='A';
LOAD XML LOCAL INFILE '33770626.xml' 
   INTO TABLE TEST01 ROWS IDENTIFIED BY '<ReasonCode>'
   SET N_ID='A';

上面的sql代码将导致下表

​

​

所有列都是VARCHAR。

我希望每个文件都有一行，所以在最后，上面的XML文件将导致..

​

​

知道怎么做到吗？

非常感谢你的时间和帮助。

Ema

Answer 1

您应该使用另一种语言来解析文件并插入数据。但是，如果只需要MySQL解决方案，则可以使用查询将数据存储到临时表中，然后使用聚合查询将数据组合到一行中并将其复制到真实的表中。

首先创建临时表：

CREATE TEMPORARY TABLE TEMP01 LIKE TEST01;

使用代码将数据从XML加载到临时表。将TEST01更改为TEMP01。

之后，用以下方式复制数据：

INSERT INTO TEST01 (N_ID, Division, RequestId, ... , StUnitId)
    SELECT N_ID
         , MIN(Division)
         , MIN(RequestId)
         ...
         , MIN(StUnitId)
    FROM TEMP01
    GROUP BY N_ID;

因为每列只有一个不同的值，所以在这里使用MIN或MAX并不重要。在MySQL 5.7中，您也可以使用ANY_VALUE。

Answer 2

您还可以选择使用像LOAD_FILE()和ExtractValue()这样的函数。注意必要的特权。

示例：

SELECT LOAD_FILE('/path/to/file/33770626.xml') INTO @`xml`;

INSERT INTO `TEST01`
SELECT
  NULLIF(TRIM(ExtractValue(@`xml`, 'MovementReport/DataArea/ToLocation/MHA')), ''),
  NULLIF(TRIM(ExtractValue(@`xml`, 'MovementReport/ControlArea/Sender/Division')), ''),
  NULLIF(TRIM(ExtractValue(@`xml`, 'MovementReport/ControlArea/RefId/Id')), ''),
  NULLIF(TRIM(ExtractValue(@`xml`, 'MovementReport/DataArea/FromLocation/Rack')), ''),
  .
  .
  .
  NULLIF(ExtractValue(@`xml`, 'MovementReport/DataArea/StUnit/StUnitId'), '');