将具有指定为属性的内容的XML导入到MySQL表中? [英] Import XML with content specified as attributes into a MySQL table?

问题描述

我有一个看起来像这样的XML文件:

I have an XML file that looks like this:

<?xml version="1.0" encoding="utf-8"?>
<comments>
  <row Id="1" PostId="1" Score="3" Text="Maxis uses this in The Sims when clicking on your person to perform actions. ex: call-&gt;friend-&gt;[friends name]" CreationDate="2010-08-09T19:52:30.690" UserId="12" />
  <row Id="2" PostId="1" Score="1" Text="Here are some examples: http://en.wikipedia.org/wiki/Pie_menu#Notable_implementations" CreationDate="2010-08-09T19:54:54.007" UserId="31" />
  <row Id="3" PostId="9" Score="13" Text="How about making it short and with less steps :)" CreationDate="2010-08-09T19:55:57.670" UserId="35" />
  <row Id="4" PostId="3" Score="1" Text="10 sec seems like an awful lot when you are browsing." CreationDate="2010-08-09T19:57:57.057" UserId="32" />
  ...
</comments>

我环顾四周，发现

I've looked around and found this slightly related question, but it has rows that look like this:

<row>
  <field name="personal_number">539</field>
  <field name="firstname">Name</field>
  <field name="lastname">Surname</field>
  <field name="email">email.domain.com</field>
  <field name="start_time">2011-04-02 13:30:00</field>
  <field name="end_time">2011-04-02 18:15:00</field>
  <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
</row>

每行的每个字段都有一个XML元素-而不是像我一样，每行都有一个将内容指定为属性的元素.

Where each row has an XML element for each field - instead of a single element for each row with the content specified as attributes, as I have.

如何将其导入MySQL?我几乎可以使用任何技术，但是我希望远离需要Windows的任何东西.

How can I import this into MySQL? I can make do with just about any technique, but I'd prefer to stay away from anything requiring Windows.

推荐答案

请参见

See http://dev.mysql.com/doc/refman/5.6/en/load-xml.html for the XML formats supported by the LOAD XML INFILE statement.

手册页指出:

导入例程会自动检测每一行的格式并正确解释.标签是根据标签或属性名称以及列名称进行匹配的.

the import routine automatically detects the format for each row and interprets it correctly. Tags are matched based on the tag or attribute name and the column name.

一种受支持的格式与您的格式类似-将列存储为<row>元素的属性.

One of the formats supported does resemble yours -- storing columns as attributes of a <row> element.

如果您需要将XML数据转换为MySQL的LOAD XML支持的格式之一，则可以使用 xsltproc .

If you need to transform your XML data into one of the formats supported by MySQL's LOAD XML, you could probably use xsltproc.

我认为，如果您的XML数据的格式不完全一致，则会使LOAD XML INFILE混乱.

I would assume that if your XML data isn't formatted with perfect consistency, that it will confuse LOAD XML INFILE.

这篇关于将具有指定为属性的内容的XML导入到MySQL表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！