sql - Storing XML into Postgres -
i have xml document needs stored in sql db (postgres). i've seen how that's done, have question: create single table xml field , place whole document there? document movies , (movies, actors...) has information later retrieved.
i've never worked xml in databases, i'm little confused.
here's example of 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 2 mob hit men, boxer, gangster's wife, , pair of diner bandits intertwine in 4 tales of violence , 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 of german bounty hunter, freed slave sets out rescue wife 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>
you can store whole xml document value in single xml column or can extract data , store in more or less normalized form.
which better, depends on details of application unknown us.
here related answer discussing pros , cons of storing document types vs. db normalization: