XML数据到PostgreSQL数据库 [英] XML data to PostgreSQL database

问题描述

将XML数据（我从网页中获取）插入PostgreSQL数据库的最好方法是什么？

我使用Java，需要一点帮助找到一个好的方法来读取这些数据（感谢Daniel Lyons指出它）

www.postgresql.org/docs/current/static/datatype-xml.html\">原始XML支持，您可以使用它来存储表。如果您希望手动切割XML数据，则在数据库中表示XML数据有不同的可能性。第一个问题应该是，如果你想要一个非常通用的解决方案，将能够存储任何XML文档或特定于您的域（即只允许某个结构的XML文档）。根据这一点，你将有一个非常灵活的，通用的表示，但是更难查询（所需的SQL将是相当复杂）。如果你有一个更具体的方法，查询会更简单，但是你需要创建新表或添加新的属性到现有的talbes每次你想存储另一种类型的文档或添加一个字段到现有的文档;因此更改模式将更加困难（这是XML的一个主要优点）。 此演示文稿应向您提供一些意见，表明不同的可能性。

此外，您可能会考虑切换到支持Xquery的某个数据库，例如 DB2 。

更新：根据您的评论，您的XML数据（您链接到）是完全关系。它可以映射1：1到下表：

  CREATE TABLE mynt（
 ID SERIAL，
 myntnafn CHAR（3），
 myntheiti Varchar（255），
 kaupgengi十进制（15,2），
 midgengi十进制（15,2），
 solugengi十进制2），
 dagsetning TimeStamp 
）
  

> mynt 标签将是表中的记录，并且相应的子标签的属性。我从您的数据收集的数据类型，他们可能是错误的。主要的问题是，IMO，没有自然的主键，所以我添加了一个自动生成的主键。

What would be the best way to insert XML data (that I get from a webpage) into PostgreSQL database?
I'm using Java and need a little help finding a good way to read this data into the database.

解决方案

Postgres has (thanks to Daniel Lyons for pointing it out) native XML support which you can use to store your table. If you however want to shred your XML data manually, there are different possibilities to represent XML data in a database. The first question should be, if you want a very generic solution, that will be able to store any XML document or one that is specific to your domain (i.e. only allows XML documents of a certain structure). Depending on that, you will have a very flexible, universal representation which is however harder to query (the SQL needed will be quite complicated). If you have a more specific approach, the queries will be simpler, but you will need to create new tables or add new attributes to existing talbes every time you want to store another type of document or add a field to an existing document; so changing the schema will be harder (which is one major advantage of XML). This presentation should give you some ideas what are the different possibilities.

Also, you might consider to switch to some DB that supports Xquery, like DB2. The ability to natively query using XQuery, a language targeted at processing XML, will simplify things a lot.

UPDATE: Given your comment, your XML data (that you linked to) is perfectly relational. It can be mapped 1:1 to the following table:

CREATE TABLE mynt (
    ID          SERIAL     ,
    myntnafn    CHAR(3)    ,
    myntheiti   Varchar(255) ,
    kaupgengi   Decimal(15,2) ,
    midgengi    Decimal(15,2) ,
    solugengi   Decimal(15,2) ,
    dagsetning  TimeStamp      
)

So any mynt tag would be a record in the table and the corresponding sub-tags the attributes. The data types I gathered from your data, they might be wrong. The main problem is, IMO, that there is no natural primary key, so I added an autogenerated one.

这篇关于XML数据到PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！