如果我的行没有“名称"，我可以使用MySQL LOAD XML LOCAL INFILE吗? [英] Can I use MySQL LOAD XML LOCAL INFILE if my rows don't have a 'name'?

问题描述

我有一些XML试图直接加载到MySQL表中.我对"ROWS IDENTIFIED BY"的想法相当满意，但是我停留在要保存元素标签和元素文本值的地方.我会尝试解释:

I have some XML that I am trying to load directly into a MySQL table. I am fairly comfortable with the idea of "ROWS IDENTIFIED BY", but I am stuck at the point where I want to save both the tag for the element and the text value of the element. I'll try to explain:

我创建了一个MySQL表:

I created a MySQL table:

CREATE TABLE stat (
  Type VARCHAR(40) NOT NULL,
  uID VARCHAR(40) NOT NULL,
  value VARCHAR(40),
  created TIMESTAMP,
  PRIMARY KEY (Type, uID)
);

现在我要加载这种格式的xml:

And now I want to load the xml which is in this format:

  <Employee uID="p17336">
    <Name>Joe Bloggs</Name>
    <Stat Type="first_name">Joe</Stat>
    <Stat Type="last_name">Bloggs</Stat>
    <Stat Type="birth_date">1985-07-26</Stat>
  </Employee>

我想要的是让每个Stat元素的类型"填充MySQL中的类型"列(此方法工作正常)，然后将该元素的实际文本(例如"Joe")填充为值" ' 柱子.我怎样才能做到这一点?目前，我正在尝试:

What I want is for the 'Type' of each Stat element to populate the 'Type' column in MySQL (this is working fine) then the actual text of the element (for example 'Joe') to populate the 'value' column. How can I do this? Currently I am trying:

 LOAD XML LOCAL INFILE 'C:/dev/Sample/employees.xml'
 INTO TABLE stat
 ROWS IDENTIFIED BY '<Stat>';

并且正在正确填充除"value"(为NULL)以外的所有字段.任何想法，不胜感激！

And am getting all fields populated correctly except 'value', which is NULL. Any thoughts much appreciated!

推荐答案

如 LOAD XML语法:

此语句支持三种不同的XML格式:

This statement supports three different XML formats:

• 列名称作为属性，列值作为属性值:

• Column names as attributes and column values as attribute values:

<row column1="value1" column2="value2" .../>

列名作为标签，列值作为这些标签的内容:

Column names as tags and column values as the content of these tags:

<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

列名是<field>标记的name属性，值是这些标记的内容:

Column names are the name attributes of <field> tags, and values are the contents of these tags:

<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

这是其他MySQL工具使用的格式，例如 mysqldump .

This is the format used by other MySQL tools, such as mysqldump.

由于XML都不是这些受支持的格式，因此您必须使用其他工具来解析它.

Since your XML is in none of these supported formats, you must use some other tool to parse it.

这篇关于如果我的行没有“名称"，我可以使用MySQL LOAD XML LOCAL INFILE吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！