在不同的 DBMS 中提取 XML 数据的最佳方法？
我正在编写一个基于 PHP 的 Web 应用程序，它使用 PDO 连接到多个数据库后端。目前它们是 MySQL 5.1+、SQLite 3.6+ 和 PostgreSQL 8/9。稍后可能会添加其他系统，只要它们足够理解我的 SQL 查询或者查询可以由我的抽象层进行转换。
现在我想以 XML 格式将日志数据存储在数据库中，因为这些日志事件的结构很大程度上取决于事件的类型。当大多数列大多数时候都是 NULL 时，我不想为任何可能的日志信息添加单独的列。
但是以后如何从数据库中取回数据呢？我需要一种一致的方法在 SELECT 查询中从 XML 文档中提取数据。我知道 MySQL 的 ExtractValue() 函数，并且我也可以通过 PDO 以某种方式向 SQLite 添加 UDF（用户定义函数）。但我还没有找到 PostgreSQL 的类似内容。然后我不知道哪种方法最好让所有 DBMS 都支持 XML，以便与相同的 SQL 查询一起使用。有人已经有解决方案或最佳实践吗？
更新：以下是此类日志记录的示例：
Num | Time | EventId | UserId | Data
1 | 2011-02-... | 1 | 42 | <data><messageid>123</messageid></data>
2 | 2011-02-... | 2 | 43 | <data><messageid>123</messageid></data>
3 | 2011-02-... | 23 | 7 | <data><oldname>006</oldname><newname>007</newname></data>
其中 EventId 描述发生的事件，例如锁定或解锁消息，或重命名用户。我可能想要查询 /data/messageid （以及设置该 XML 值的适当 EventId）以查找所有讲述我想要绘制其审核历史记录的特定消息的事件。
它不是像整个网页那样的大型 XML 文档，只是一种很好的结构化方式来保存所有那些我没有专用表列的值。而且它是可扩展的，因此如果我想象一个新的 EventId 编号，我可以存储该事件的任何数据块。 XML 值的条件绝不是我的查询的唯一条件。应首先根据其他标准（例如时间跨度或 EventId 列表）缩小行范围。
I'm writing a PHP-based web application that uses PDO to connect to multiple database backends. Currently they are MySQL 5.1+, SQLite 3.6+ and PostgreSQL 8/9. Other systems could possibly be added later, given they understand my SQL queries well enough or the queries can be transformed by my abstraction layer.
Now I'd like to store log data in the database in XML format as the structure of these log events highly depends on the type of the event. I wouldn't want to add a separate column for any possible log information when most columns are NULL most of the time.
But how can I get the data back from the database later? I need a consistent way to extract data from the XML documents in a SELECT query. I know of MySQL's ExtractValue() function and I could add a UDF (user defined function) to SQLite through PDO somehow as well. But I haven't found anything similar for PostgreSQL. And then I don't know which way would be the best to get XML support into all DBMS for use with the same SQL query. Does anybody have a solution or best practices for that already?
Update: Here's an example of such a log record:
Num | Time | EventId | UserId | Data
1 | 2011-02-... | 1 | 42 | <data><messageid>123</messageid></data>
2 | 2011-02-... | 2 | 43 | <data><messageid>123</messageid></data>
3 | 2011-02-... | 23 | 7 | <data><oldname>006</oldname><newname>007</newname></data>
Where EventId describes the event that happened, like locking or unlocking a message, or renaming a user. I'd possibly want to query for /data/messageid (together with appropriate EventIds that set that XML value) to find all events that tell about a particular message of which I want to plot the moderation history.
It's no big XML documents like entire web pages, just a nice structured way to keep all those values that I have no dedicated table column for. And it's extensible, so if I imagine a new EventId number, I can store any chunk of data with that event. By no means shall a condition on an XML value be the only condition of my queries. The rows shall be narrowed down (largely) by other criteria first, like a time span or a list of EventIds.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您正在努力实现数据库独立性，那么在数据库中存储 XML 将会给您的抽象层带来相当大的负担，因为不幸的是，每个 DBMS 处理它的方式都不同。这并不是说它无法完成，但您必须做大量工作来研究每个受支持的数据库中的 XML 功能（或缺乏这些功能）。 （而且我认为 SQLite 根本没有任何 XML 功能...）
如果您绝对必须使用 XML，XQuery 几乎是查询原始 XML 的理想方法，但关系数据库不支持它。 SQL/XML 是其中查询 XML 的一种标准，但尚未得到广泛实施。
否则，表中存在大量空值实际上并没有什么坏处。想必您的应用程序代码无论如何都必须检查 null 值，无论它们来自数据库表还是来自不存在的 XML 元素...
If you are striving for database independence, storing XML in your database is going to put quite a burden on your abstraction layer, because every DBMS handles it differently, unfortunately. That isn't to say it can't be done, but you're going to have to do a lot of work researching the XML capabilities (or lack thereof) in each of your supported databases. (And I don't think SQLite has any XML features at all...)
If you absolutely have to use XML, XQuery is pretty much the ideal method for querying raw XML, but it's not supported by relational databases. SQL/XML is one standard for querying XML in these, but it's not widely implemented.
Otherwise, there is really no harm in having a lot of null values in a table. Presumably your application code is going to have to check for null values anyway, whether they come from a database table or from a nonexistent XML element...
我会远离数据库提供的 XML 功能，并将任何 XML 存储为 BLOB。这可能意味着您必须复制一些数据；基本上任何你想查询的东西。如果您希望所有内容都可查询，那么最好使用本机 XML 数据库。
XML 扩展通常只是美观的原因是，即使服务器解析 XML，它仍然需要通过网络发送它；再次解码或解析。因此，通常最好将其作为字节序列流式传输并在客户端处理解析。唯一的例外是如果您想使用基于 XML 的访问方法（xpath 或 xquery 查找）；但这就是本机 XML 数据库比关系数据库更适合的地方（“猪身上的口红”）。
最后，更常见的双重设置是将数据库与搜索索引系统（如 Lucene、Elastic Search）相结合，而不是结合 XML 和关系数据库；这为您提供了强大的免费文本搜索和存储空间。当数据库中的数据被修改时，搜索索引会增量更新。
I would stay away from DB provided XML functionality, and store any XML as BLOB. This may mean you have to duplicate some of data; basically anything you want to query. If you want all of it queryable, you would be better off with a native XML database.
The reason XML extensions are often just eye candy is that even if server parses XML, it still needs to send it over the wire; to be decoded or parsed again. So it is often better to just stream it down as byte sequence and handle parsing on client side. The only exception would be if you wanted to use XML-based access methods (xpath or xquery lookups); but that's where native XML databases are much better fit than relational DBs ("lipstick on a pig").
Finally, instead of combining XML and relational DB, a more common dual setup is to combine database with a search index system (like Lucene, Elastic Search); this gives you powerful free text search along with storage. Search index is then incrementally updated when data in database is modified.