Storing XML into Postgres

Question

I have an XML document that needs to get stored in an SQL db (Postgres). I've already seen how that's done, but I have a question: do I just create a single table with a xml field and place the whole document there? This is a document about movies and so (movies, actors...) that has information to be later retrieved.

I've never worked with XML in databases, so I'm a little confused.

Here's an example of my XML:

<?xml version="1.0" encoding="UTF-8"?>
<cinema xmlns="movies"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="movies file:/C:/Users/Fabio/git/LAPD/movies.xsd">
<persons>
    <person id="P1">
        <name>Samuel L. Jackson</name>
        <birth>1948-12-21</birth>
    </person>
    <person id="P2">
        <name>Leonardo Di Caprio</name>
        <birth>1974-11-11</birth>
    </person>
    <person id="P3">
        <name>Quentin Tarantino</name>
        <birth>1963-03-27</birth>
    </person>
</persons>
<movies>
    <movie id="M1">
        <title>Pulp Fiction</title>
        <length>154</length>
        <year>1994</year>
        <description>The lives of two mob hit men, 
            a boxer, a gangster's wife, and a pair 
            of diner bandits intertwine in four tales of violence and redemption</description>
        <crew>
            <director ref="P3"/>
            <writer ref="P3"/>
        </crew>
        <cast>
            <actor ref="P1"/>
        </cast>
        <rate>
            <imdb>8.9</imdb>
            <rottentomatoes>9</rottentomatoes>
            <moviedb>7.8</moviedb>
            <average>8.57</average>
        </rate>
        <numOscars>1</numOscars>
    </movie>
    <movie id="M2">
        <title>Django Unchained</title>
        <length>165</length>
        <year>2012</year>
        <description>With the help of a German bounty hunter, 
            a freed slave sets out to rescue his wife 
            from a brutal Mississippi plantation owner.</description>
        <crew>
            <director ref="P3"/>
            <writer ref="P3"/>
        </crew>
        <cast>
            <actor ref="P1"/>
            <actor ref="P2"/>
        </cast>
        <rate>
            <imdb>8.5</imdb>
            <rottentomatoes>8</rottentomatoes>
            <moviedb>7.4</moviedb>
            <average>7.97</average>
        </rate>
        <numOscars>2</numOscars>
    </movie>
</movies>

Community · Accepted Answer · 2017-05-23 11:58:30Z

7

You can store a whole XML document as value in a single xml column or you can extract data and store it in a more or less normalized form.

Which is better, depends on all the details of your application that are unknown to us.

Here is a related answer discussing pros and cons of storing document types vs. db normalization:

Does JSONB make PostgreSQL arrays useless?

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 9, 2015 at 23:46

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fabio Over a year ago

I see. I added an instance. As you can see, it's a more or less simple structure, but some elements are referred by others, so I don't know if I can separate them...

Erwin Brandstetter Over a year ago

@Fabio: There is no definitive answer. You'll have to decide which way to go based on the complete picture of your application. I added a link to a related answer.

Fabio Over a year ago

Thanks, I'm gonna check it

Himanshu Joshi · Accepted Answer · 2021-01-25 14:22:09Z

1

Save XML as a text column of DB so that you can also apply equality operator easily. You may find some error on insertion for " or ' so try to replace them with other characters like ~ or `, both.

answered Jan 25, 2021 at 14:22

Himanshu Joshi

1361 silver badge6 bronze badges

Collectives™ on Stack Overflow

Storing XML into Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related