MySQL 查询的 XML 输出

Question

是否有机会将 MySQL 查询的输出直接获取为 XML？

我指的是 MSSQL 具有 SQL-XML 插件的内容，例如：

SELECT * FROM table WHERE 1 FOR XML AUTO

返回文本（或准确地说，是 MSSQL 中的 xml 数据类型），其中包含生成的 XML 标记结构 根据表中的列。

使用 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

它会生成如下 XML 代码：

<employee emp_id='129'>
   <customer cust_id='107' region='Eastern'/>
</employee>

您有任何线索如何在MySQL？

预先感谢您的回答。

Answer 1

mysql命令可以直接输出XML，使用--xml 选项，该选项至少早在 MySql 4.1 就可用。

但是，这不允许您自定义 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>

您想要：

<?xml version="1.0"?>
<orders>
  <employee emp_id="129">
    <customer cust_id="107" region="Eastern"/>
  </employee>
</orders>

可以使用脚本通过 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>

这显然比简洁的 MSSQL 语法更冗长，但另一方面它更强大，可以完成 MSSQL 中不可能完成的各种事情。

如果您使用命令行 XSLT 处理器（例如 xsltproc 或 saxon），则可以将 mysql 的输出直接通过管道传输到 XSLT 程序中。例如：

mysql -e 'select * from table' -X database | xsltproc script.xsl -

Answer 2

在 MySQL 中使用 XML 似乎是一个很好的起点从 MySQL 查询到 XML 的不同方法。

来自文章：

   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 ();

Answer 3

你知道如何在 MySQL 中实现这一点吗？

是的，步行并使用 CONCAT 字符串自行制作 xml。尝试

SELECT concat(';') FROM table

我从 2009 年的答案中获取了这个如何转换 MySQL DB 到 XML？ 它似乎仍然有效。不是很方便，如果每个项目都有很大的树，它们都将位于根项目的一个串联值中，但它有效，请使用虚拟模型查看此测试：

SELECT concat('<客户 cust_id="', 2, '" 区域="', 3, '"/>') FROM DUAL< /code>

给出

通过“手动编码”，您可以得到这个结构。

<?xml version="1.0"?>
<orders>
  <employee emp_id="1">
    <customer cust_id="2" region="3" />
  </employee>
</orders>

我用每个根项更大的树对此进行了检查，它起作用了，但我必须在其上运行额外的 Python 代码，以消除当 xml 路径中具有中等级别节点时生成的过多开口和闭合。可以将向后查找列表与临时集中的条目一起使用，我已经完成了，但是面向对象的方式会更专业。我只是编写代码，以便在找到新的头项后立即从列表中删除最后 x 项，以及嵌套分支的一些其他技巧。工作了。

我想出了一个找到标签之间每个文本的正则表达式：

string = "     <some tag><another tag>test string<another tag></some tag>"
pattern = r'(?:^\s*)?(?:(?:<[^\/]*?)>)?(.*?)?(?:(?:<\/[^>]*)>)?'
p = re.compile(pattern)
val = r''.join(p.findall(string))
val_escaped = escape(val)
if val_escaped != val:
    string.replace(val, val_escaped)

这个正则表达式可以帮助您访问标签之间的文本。如果允许您使用 CDATA，那么在任何地方使用它都是最简单的。只需将内容“CDATA”（字符数据）添加到 MySQL 中即可：

<Title><![CDATA[', t.title, ']]></Title>

除了非常奇怪的字符（如 (U+001A)）之外，您将不再遇到任何问题，您应该在 MySQL 中替换这些字符。然后，您根本不需要关心转义和替换其余特殊字符。在 1 Mio 上为我工作。 lines xml 文件大量使用特殊字符。

然而：您应该使用 Python 的模块 xmlschema 根据所需的 xml 模式文件验证该文件。当您不被允许使用该 CDATA 技巧时，它会提醒您。

如果您需要不带 CDATA 的完全 UTF-8 格式的内容（这通常是一项任务），那么即使在 1 Mio 行文件中，您也可以通过根据 xml 模式文件逐步验证代码输出（= xml 输出）来实现这一目标（ xsd 这就是目标）。这是一项有点繁琐的工作，但只要有耐心就可以完成。
可以使用以下方式进行替换：

• MySQL 使用replace()
• Python 使用 string.replace()
• Python 使用 Regex 替换（虽然我最终不需要它，但它会看起来像：re.sub (re.escape(val), 'xyz', i))
• string.encode(encoding = 'UTF-8',Errors = 'strict')

请注意，编码为 utf-8 是最强大的步骤，它甚至可以抛开上述所有其他三种替代方式。还要注意：它使文本成为二进制，然后您需要将其视为二进制 b'...'，因此您可以使用 wb 仅以二进制模式将其写入文件。

最后，您可以在普通浏览器（如 Firefox）中打开 XML 输出进行最终检查并观察 XML 的工作情况。或者使用 xml 扩展在 vscode/codium 中检查它。但这些检查是不需要的，在我的例子中，xmlschema 模块已经很好地显示了一切。还要注意的是，vscode/codium 可以很容易地处理 xml 问题，并且当 Firefox 不能时仍然显示树，因此，您将需要一个验证器或浏览器来查看所有 xml 错误。

使用这个 xml-building-with-mysql 可以完成一个相当大的项目，最后有一个三重嵌套的 xml 树，父节点内有许多重复标签，全部由二维 MySQL 输出组成。

PS

除了 MySQL，您可能更适合使用 MSSQL 服务器等内置工具。这个答案只是关于 XML 构建的手艺，但现在有更好的工具。如果将 XML 混淆在一起需要花费太多时间和精力，请导出到文件并在另一个 SQL 中对其进行处理。