How to Create New Xml File from Existing Database in Postgresql Database Using Java

XML data to PostgreSQL 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.

Create XML from PostgreSQL?

By using the XML functions:

http://www.postgresql.org/docs/current/static/functions-xml.html#AEN15086

Creating a database changelog xml file from an existing database (including stored procs) using Liquibase

You can return the data using a diffTypes flag that includes "DATA". See http://www.liquibase.org/documentation/diff.html.

Liquibase cannot currently output stored procedures, however. For that you will need to use a different tool and include them in the generated changelog using the tag.

How to insert data from xml document to postgresql?

Probably best to generate java classes for the XML from the XSD, you can read about it here:
Generate Java classes from .XSD files...?

Access XML file from another server and store data to my database

You can use the following Java code inside a servlet (if you have one) or inside your jsp file (inside a scriptlet) but that would make for a terrible design

 public String getXmlFromUrl(String url)    {

    BufferedReader reader = null;
    StringBuilder stringBuilder=null;
    String lineSeparator = System.getProperty("line.separator");

    try{
        URL url = new URL(url);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setDoInput(true);
        conn.setRequestMethod("GET");
        conn.setRequestProperty("Content-type", "application/xml");
        conn.setReadTimeout(10000);//set timeout to 10000 or whatever you think is OK
        conn.connect();
        reader = new BufferedReader(new InputStreamReader(conn.getInputStream(),"UTF-8"));
        stringBuilder = new StringBuilder();
        String line=null;
        while((line = reader.readLine()) != null){
            stringBuilder.append(line + lineSeparator);
        }

    catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        if(reader!=null)
            try {
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
    }
    return stringBuilder.toString();
}

For inserting the xml into your database, use JDBC. If you don't Know how to do that , just Search SO, there's plenty of examples.

How can I store XML data?

jOOQ 3.7 doesn't support XML (or JSON, etc.) types out of the box, but indeed, jOOQ helps you abstract the XML data type by implementing your custom data type binding. There's an example for JSON and PostgreSQL in the manual. You can adapt that easily for XML:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

Essentially, you'll need to implement org.jooq.Binding<Object, YourXMLRepresentation>, and then configure the code generator to apply this binding either to all XML type columns, or to columns of a specific name pattern:

http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

Load simple XML file into Postgres

Well, it can be done. I wrote a complete function for the same purpose a while back:

• XML data to PostgreSQL database

I use pg_read_file() to read in the whole file into a variable in plpgsql and proceed from there. This carries a few restrictions:

Only files within the database cluster directory and the log_directory
can be accessed. Use a relative path for files in the cluster
directory, and a path matching the log_directory configuration setting
for log files. Use of these functions is restricted to superusers.

But I listed alternatives.

If you can convert the XML file to JSON it gets simpler. There are a bunch of tools for that available.

Starting with Postgres 9.3 you get pretty good support for the json data type.

Better yet, use the jsonb data type in Postgres 9.4+.

• How do I query using fields inside the new PostgreSQL JSON datatype?

---

Related Topics