MySQL:从格式异常的 XML 文件中提取数据

Question

我有一个 XML 文件，其中的条目如下所示:

<card name="Fire Toad" id="7366070c-c74d-4bb9-b3d8-23177e887073">
    <property name="Sphere" value="Nature" />
    <property name="Cost" value="1" />
    <property name="Type" value="Creature" />
    <property name="Subtype" value="Animal Toad" />
    <property name="Attack" value="3" />
    <property name="Defense" value="2" />
    <property name="Gems" value="B" />
    <property name="Rules" value="" />
    <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing." />
    <property name="Rarity" value="Common" />
    <property name="Series" value="1" />
    <property name="Number" value="106" />
    <property name="Illustrator" value="Kevin Shoemaker" />
</card>

我想从中提取数据(对于每张卡:名称、ID 和所有属性)并将其插入 MySQL 数据库的表中。如果我可以使用内置的 LOAD XML 语句，那就太好了，但不幸的是它不支持我的文件格式。最方便的方法是什么？

Answer 1

考虑XSLT它与 SQL 很相似，是一种声明性的、专用的语言，但旨在将 XML 源转换为特定格式，例如 MySQL LOAD XML 所需的 XML。方法。 XSLT 可以通过 Bash/Powershell、任何通用语言(Java、C#、PHP、Perl、Python、R、VB)、专用处理器(例如 Saxon/Xalan)通过命令行运行。 ，甚至是您日常使用的 Excel!

事实上，您甚至可以通过向终端发送 shell 命令来直接从 MySQL 的命令行客户端运行 XSLT。这是一个xsltproc (适用于 Linux/Mac 的处理器)调用:

mysql> \! xsltproc -o /path/to/Output.xml /path/to/Script.xsl /path/to/Input.xml

下面的脚本解析到card节点并将property子节点迁移到新元素和值。

XSLT (另存为 .xsl，一种特殊的 .xml 文件)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/root">  
        <data> 
            <xsl:apply-templates select="cards"/>
        </data>
    </xsl:template>

    <xsl:template match="cards">  
        <xsl:apply-templates select="card"/>
    </xsl:template>

    <xsl:template match="card"> 
        <row>
           <name><xsl:value-of select="@name"/></name>
           <id><xsl:value-of select="@id"/></id>
           <xsl:apply-templates select="property"/>
        </row>       
    </xsl:template>

    <xsl:template match="property">
    <xsl:element name="{@name}"> 
        <xsl:value-of select="@value"/>
    </xsl:element>
    </xsl:template>

</xsl:stylesheet>

输入 XML (假设采用这种格式的结构，调整 XSLT 中的根名称)

<?xml version="1.0"?>
<root>
  <more_nodes/>
  <still_nodes/>
  <cards>
    <card name="Fire Toad 1" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 1"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 1"/>
      <property name="Subtype" value="Animal Toad 1"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B1"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 1"/>
      <property name="Rarity" value="Common 1"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 1"/>
    </card>
    <card name="Fire Toad 2" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 2"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 2"/>
      <property name="Subtype" value="Animal Toad 2"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B2"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 2"/>
      <property name="Rarity" value="Common 2"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 2"/>
    </card>
    <card name="Fire Toad 3" id="7366070c-c74d-4bb9-b3d8-23177e887073">
      <property name="Sphere" value="Nature 3"/>
      <property name="Cost" value="1"/>
      <property name="Type" value="Creature 3"/>
      <property name="Subtype" value="Animal Toad 3"/>
      <property name="Attack" value="3"/>
      <property name="Defense" value="2"/>
      <property name="Gems" value="B3"/>
      <property name="Rules" value=""/>
      <property name="Flavor" value="Fire toads have the uncanny ability to cook their food while chewing. 3"/>
      <property name="Rarity" value="Common 3"/>
      <property name="Series" value="1"/>
      <property name="Number" value="106"/>
      <property name="Illustrator" value="Kevin Shoemaker 3"/>
    </card>
  </cards>
  <other_nodes/>
</root>

输出 XML (为 MySQL 的 LOAD XML 做好准备)

<?xml version="1.0"?>
<data>
  <row>
    <name>Fire Toad 1</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 1</Sphere>
    <Cost>1</Cost>
    <Type>Creature 1</Type>
    <Subtype>Animal Toad 1</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B1</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 1</Flavor>
    <Rarity>Common 1</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 1</Illustrator>
  </row>
  <row>
    <name>Fire Toad 2</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 2</Sphere>
    <Cost>1</Cost>
    <Type>Creature 2</Type>
    <Subtype>Animal Toad 2</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B2</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 2</Flavor>
    <Rarity>Common 2</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 2</Illustrator>
  </row>
  <row>
    <name>Fire Toad 3</name>
    <id>7366070c-c74d-4bb9-b3d8-23177e887073</id>
    <Sphere>Nature 3</Sphere>
    <Cost>1</Cost>
    <Type>Creature 3</Type>
    <Subtype>Animal Toad 3</Subtype>
    <Attack>3</Attack>
    <Defense>2</Defense>
    <Gems>B3</Gems>
    <Rules/>
    <Flavor>Fire toads have the uncanny ability to cook their food while chewing. 3</Flavor>
    <Rarity>Common 3</Rarity>
    <Series>1</Series>
    <Number>106</Number>
    <Illustrator>Kevin Shoemaker 3</Illustrator>
  </row>
</data>