MySQL 的 XML 输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2847674/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XML output from MySQL
提问by NumberFour
is there any chance of getting the output from a MySQL query directly to XML?
是否有机会将 MySQL 查询的输出直接转换为 XML？
Im referring to something like MSSQL has with SQL-XMLplugin, for example:
我指的是像 MSSQL 与SQL-XML插件一样的东西，例如：
SELECT * FROM table WHERE 1 FOR XML AUTO
returns text (or xml data type in MSSQL to be precise) which contains an XML markup structure generated according to the columns in the table.
返回包含根据表中的列生成的 XML 标记结构的文本（或 MSSQL 中的 xml 数据类型）。
With SQL-XMLthere is also an option of explicitly defining the output XML structure like this:
使用SQL-XML还可以选择显式定义输出 XML 结构，如下所示：
SELECT
1 AS tag,
NULL AS parent,
emp_id AS [employee!1!emp_id],
cust_id AS [customer!2!cust_id],
region AS [customer!2!region]
FROM table
FOR XML EXPLICIT
which generates an XML code as follows:
其生成的 XML 代码如下：
<employee emp_id='129'>
<customer cust_id='107' region='Eastern'/>
</employee>
Do you have any clues how to achieve this in MySQL?
你有什么线索如何在 MySQL 中实现这一点吗？
Thanks in advance for your answers.
预先感谢您的回答。
采纳答案by soldier.moth
Using XML with MySQLseems to be a good place to start with various different ways to get from MySQL query to XML.
在 MySQL 中使用 XML似乎是一个很好的起点，可以从各种不同的方式开始从 MySQL 查询到 XML。
From the article:
从文章：
use strict;
use DBI;
use XML::Generator::DBI;
use XML::Handler::YAWriter;
my $dbh = DBI->connect ("DBI:mysql:test",
"testuser", "testpass",
{ RaiseError => 1, PrintError => 0});
my $out = XML::Handler::YAWriter->new (AsFile => "-");
my $gen = XML::Generator::DBI->new (
Handler => $out,
dbh => $dbh
);
$gen->execute ("SELECT name, category FROM animal");
$dbh->disconnect ();
回答by Neil Mayhew
The mysqlcommand can output XML directly, using the --xmloption, which is available at least as far back as MySql 4.1.
该mysql命令可以使用--xml选项直接输出 XML，该选项至少可以追溯到 MySql 4.1。
However, this doesn't allow you to customize the structure of the XML output. It will output something like this:
但是，这不允许您自定义 XML 输出的结构。它将输出如下内容：
<?xml version="1.0"?>
<resultset statement="SELECT * FROM orders" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="emp_id">129</field>
<field name="cust_id">107</field>
<field name="region">Eastern</field>
</row>
</resultset>
And you want:
你想要：
<?xml version="1.0"?>
<orders>
<employee emp_id="129">
<customer cust_id="107" region="Eastern"/>
</employee>
</orders>
The transformation can be done with XSLTusing a script like this:
可以使用XSLT使用如下脚本完成转换：
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="resultset">
<orders>
<xsl:apply-templates/>
</orders>
</xsl:template>
<xsl:template match="row">
<employee emp_id="{field[@name='emp_id']}">
<customer
cust_id="{field[@name='cust_id']}"
region="{field[@name='region']}"/>
</employee>
</xsl:template>
</xsl:stylesheet>
This is obviously way more verbose than the concise MSSQL syntax, but on the other hand it is a lot more powerful and can do all sorts of things that wouldn't be possible in MSSQL.
这显然比简洁的 MSSQL 语法更冗长，但另一方面，它更强大，可以完成 MSSQL 中无法实现的各种事情。
If you use a command-line XSLT processor such as xsltprocor saxon, you can pipe the output of mysqldirectly into the XSLT program. For example:
如果您使用命令行 XSLT 处理器，例如xsltproc或saxon，则可以将 的输出mysql直接通过管道传送到 XSLT 程序中。例如：
mysql -e 'select * from table' -X database | xsltproc script.xsl -
