Thoughts on storing address data with XML data type?

Hello.

I'm pretty new to SQL Server (switching from Visual FoxPro).

I'm developing a database that will store, amongst other things, names
and addresses of people from all over the world (approx 100,000
records). Up to now, I've always used a flat model of storing address
data using fields:

Address line 1
Address line 2
Town/City
County/State
Postcode/ZIP
Country

But this model doesn't cater very well for the intricacies of postal
systems all over the world. There is also redundancy, particularly with
fields Line2, County/State, Postcode/ZIP, which are not needed for many
countries.

It also occurs to me that using multiple fields like this in a table
with several fields gives no indication of the linked nature of the
information. For example, if the town field is changed, then chances
are that some of the other lines of the address become obsolete unless
they are changed at the same time.

So I have been thinking about storing the address in an XML structure
in a single field. This will allow me, where necessary, to break down
address data into smaller units where known (e.g. premise name, premise
number, apartment number), or if a breakdown is not known to store
chunks of data together. It also then allows me the flexibility to
re-build the data in an appropriate order (e.g. for an address label)
relevant for the particular country/language - for example number
before/after street; postcode before town/separate line at the end.

An example fragment might be:

<address>
<streetaddress>
<premisename>Oak Cottage</premisename>
<premisenumber>15</premisenumber>
<streetname>Greenhill</streetname>
<streettype>Crescent</streettype>
</streetaddress>
<locality>
<village>Little Hampton</village>
<town>Rickmansworth</town>
<county>Hertfordshire</county>
</locality>
<postcode>WD3 3UH</postcode>
<country>UK</country>
</address>


Bearing in mind some of this data will need to be searchable
(especially postcode, country, and possibly town), are the possible
benefits of this going to be outweighed by vast amounts of extra work,
and a performance penalty, or does it sound like a good practical use
of the XML data type?

Any comments gratefully appreciated.

Ben

Hi

Your plan sounds like quite reasonable. You don't need to choose one of the
two alternatives. Using property promotion, you can utilize benefits of each
data structures. This document may help.

http://msdn2.microsoft.com/en-us/library/ms187508.aspx

--
Pohwan Han. Seoul. Have a nice day.
<ben.hambidge> wrote in message
news:1149328968.862225.161390@i40g2000cwc.googlegr oups.com...
> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben
>

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.pizza.divertito.info http://www.teatro.rumore.org http://www.milano.divertito.info http://www.divertito.info http://www.rumore.org http://www.pornografia.apoteosi.org http://www.juventus.divertito.info http://www.informatica.rumore.org http://www.festa.rumore.org http://www.abiti-da-sposa.apoteosi.org http://www.casa.apoteosi.org http://www.apoteosi.org http://www.abiti-da-sposa.apoteosi.org http://www.rumore.org http://www.festa.rumore.org http://www.casa.apoteosi.org http://www.teatro.rumore.org http://www.pizza.divertito.info http://www.apoteosi.org http://www.informatica.rumore.org http://www.juventus.divertito.info http://www.divertito.info http://www.milano.divertito.info http://www.pornografia.apoteosi.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.musica-latina.inscena.info http://www.casa.inscena.info http://www.bimbo.frapoco.info http://www.nudismo.frapoco.info http://www.culo.presentazione.org http://www.hilary-duff.inscena.info http://www.virgilio.frapoco.info http://www.alitalia.frapoco.info http://www.troie.presentazione.org http://www.playboy.inscena.info http://www.nuoto.inscena.info http://www.uomo.inscena.info http://www.meteo.inscena.info http://www.yahoo.presentazione.org http://www.liberi.presentazione.org http://www.wallpaper.presentazione.org http://www.italiano.presentazione.org http://www.wikipedia.presentazione.org http://www.google.inscena.info http://www.erotismo.inscena.info http://www.pornografia.frapoco.info http://www.canzoni.inscena.info http://www.abiti-da-sposa.frapoco.info http://www.figa.frapoco.info http://www.tiscali.presentazione.org http://www.porn.frapoco.info http://www.jesse-mccartney.presentazione.org http://www.totti.frapoco.info http://www.musica-gratis.presentazione.org http://www.repubblica.frapoco.info

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.elogio.org http://www.video-de-musica.elogio.org http://www.materazzi-zidane.oscurita.org http://www.juventus.oscurita.org http://www.danni.elogio.org http://www.fiorentina.elogio.org http://www.porn.colpo.org http://www.alessandra.oscurita.org http://www.sicilia.colpo.org http://www.pornografia.colpo.org http://www.milano.elogio.org http://www.liberi.colpo.org http://www.bambino.elogio.org http://www.oscurita.org http://www.colpo.org http://www.gazzetta.oscurita.org http://www.tattoo.oscurita.org http://www.serie-a.colpo.org http://www.fiorentina.elogio.org http://www.bambino.elogio.org http://www.video-de-musica.elogio.org http://www.alessandra.oscurita.org http://www.juventus.oscurita.org http://www.oscurita.org http://www.serie-a.colpo.org http://www.milano.elogio.org http://www.colpo.org http://www.materazzi-zidane.oscurita.org http://www.pornografia.colpo.org http://www.porn.colpo.org http://www.danni.elogio.org http://www.sicilia.colpo.org http://www.liberi.colpo.org http://www.tattoo.oscurita.org http://www.elogio.org http://www.gazzetta.oscurita.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.bella.presentazione.org http://www.pornografia.presentazione.org http://www.topless.inscena.info http://www.ragazzi.inscena.info http://www.totti.inscena.info http://www.sexy.frapoco.info http://www.repubblica.presentazione.org http://www.pene.inscena.info http://www.azzurra.presentazione.org http://www.tinto-brass.frapoco.info http://www.fiori.presentazione.org http://www.natale.presentazione.org http://www.concerto.inscena.info http://www.uomo.presentazione.org http://www.wikipedia.frapoco.info http://www.bikini.frapoco.info http://www.trenitalia.presentazione.org http://www.dizionario.inscena.info http://www.serie-a.presentazione.org http://www.giorgia-palmas.presentazione.org http://www.informatica.inscena.info http://www.calciomercato.frapoco.info http://www.curriculum.inscena.info http://www.filosofia.frapoco.info http://www.solo.presentazione.org http://www.valentino.inscena.info http://www.virgilio.presentazione.org http://www.tiscali.inscena.info http://www.politica.inscena.info http://www.abiti-da-sposa.inscena.info http://www.giochi.frapoco.info http://www.corriere-della-sera.inscena.info http://www.hentay.frapoco.info http://www.viagra.presentazione.org http://www.anne-geddes.frapoco.info http://www.poesia.presentazione.org http://www.musica.presentazione.org http://www.wallpaper.frapoco.info http://www.culo.inscena.info http://www.tempo.frapoco.info http://www.erotismo.frapoco.info http://www.teatro.frapoco.info http://www.cristiana.frapoco.info http://www.biglietti.frapoco.info http://www.sesso.inscena.info

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.sex.spiovente.org http://www.nuoto.spiovente.org http://www.formula-1.spiovente.org http://www.credito.introdurre.org http://www.barzellette.puntata.org http://www.venezia.spiovente.org http://www.porno.introdurre.org http://www.nudismo.introdurre.org http://www.italia.introdurre.org http://www.canzoni.introdurre.org http://www.yahoo.spiovente.org http://www.musica-gratis.introdurre.org http://www.jesse-mccartney.puntata.org http://www.ringtone.spiovente.org http://www.shakira.introdurre.org http://www.bambini.introdurre.org http://www.liberi.introdurre.org http://www.alitalia.introdurre.org http://www.testi.puntata.org http://www.troie.spiovente.org http://www.ecologia.puntata.org http://www.meteo.introdurre.org http://www.playboy.spiovente.org http://www.porn.introdurre.org http://www.bimbo.introdurre.org http://www.keira-knightley.puntata.org http://www.ebay.spiovente.org http://www.bambino.puntata.org http://www.calcio.spiovente.org http://www.pompini.spiovente.org http://www.italiano.puntata.org http://www.universita.puntata.org http://www.turismo.introdurre.org http://www.babbo.puntata.org http://www.amore.spiovente.org http://www.nuda.spiovente.org http://www.musica-latina.puntata.org http://www.sudoku.puntata.org http://www.pagine-bianche.puntata.org http://www.milano.puntata.org http://www.bianchi.puntata.org http://www.mare.spiovente.org http://www.torino.introdurre.org http://www.figa.puntata.org http://www.google.spiovente.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.palermo.spiovente.org http://www.gianna.introdurre.org http://www.firenze.spiovente.org http://www.casa.puntata.org http://www.lazio.spiovente.org http://www.ryanair.puntata.org http://www.inter.introdurre.org http://www.erotismo.puntata.org http://www.concerto.puntata.org http://www.sudoku.spiovente.org http://www.rimini.puntata.org http://www.prima.introdurre.org http://www.lippi.puntata.org http://www.manda.spiovente.org http://www.gazzetta.puntata.org http://www.piccolo.introdurre.org http://www.verona.introdurre.org http://www.francesca.puntata.org http://www.tiscali.spiovente.org http://www.zucchero.puntata.org http://www.sesso.introdurre.org http://www.nuoto.introdurre.org http://www.umbria.spiovente.org http://www.calabria.spiovente.org http://www.credito.puntata.org http://www.mondo.introdurre.org http://www.universita.spiovente.org http://www.valentino.puntata.org http://www.bella.puntata.org http://www.valentino-rossi.introdurre.org http://www.wikipedia.introdurre.org http://www.genova.spiovente.org http://www.viaggio.puntata.org http://www.erotico.spiovente.org http://www.primo.introdurre.org http://www.curriculum.introdurre.org http://www.traduttore.spiovente.org http://www.tattoo.introdurre.org http://www.danni.puntata.org http://www.solo.introdurre.org http://www.filosofia.introdurre.org http://www.messina.spiovente.org http://www.pokemon-porn.spiovente.org http://www.winnie-the-pooh.spiovente.org http://www.bologna.puntata.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

> Hello.
>
> I'm pretty new to SQL Server (switching from Visual FoxPro).
>
> I'm developing a database that will store, amongst other things, names
> and addresses of people from all over the world (approx 100,000
> records). Up to now, I've always used a flat model of storing address
> data using fields:
>
> Address line 1
> Address line 2
> Town/City
> County/State
> Postcode/ZIP
> Country
>
> But this model doesn't cater very well for the intricacies of postal
> systems all over the world. There is also redundancy, particularly with
> fields Line2, County/State, Postcode/ZIP, which are not needed for many
> countries.
>
> It also occurs to me that using multiple fields like this in a table
> with several fields gives no indication of the linked nature of the
> information. For example, if the town field is changed, then chances
> are that some of the other lines of the address become obsolete unless
> they are changed at the same time.
>
> So I have been thinking about storing the address in an XML structure
> in a single field. This will allow me, where necessary, to break down
> address data into smaller units where known (e.g. premise name, premise
> number, apartment number), or if a breakdown is not known to store
> chunks of data together. It also then allows me the flexibility to
> re-build the data in an appropriate order (e.g. for an address label)
> relevant for the particular country/language - for example number
> before/after street; postcode before town/separate line at the end.
>
> An example fragment might be:
>
> <address>
> <streetaddress>
> <premisename>Oak Cottage</premisename>
> <premisenumber>15</premisenumber>
> <streetname>Greenhill</streetname>
> <streettype>Crescent</streettype>
> </streetaddress>
> <locality>
> <village>Little Hampton</village>
> <town>Rickmansworth</town>
> <county>Hertfordshire</county>
> </locality>
> <postcode>WD3 3UH</postcode>
> <country>UK</country>
> </address>
>
>
> Bearing in mind some of this data will need to be searchable
> (especially postcode, country, and possibly town), are the possible
> benefits of this going to be outweighed by vast amounts of extra work,
> and a performance penalty, or does it sound like a good practical use
> of the XML data type?
>
> Any comments gratefully appreciated.
>
> Ben

http://www.fare.elogio.org http://www.formula-1.colpo.org http://www.pornografia.oscurita.org http://www.venezia.elogio.org http://www.jesse-mccartney.elogio.org http://www.teatro.colpo.org http://www.egipto.elogio.org http://www.palermo.oscurita.org http://www.sex.colpo.org http://www.sardinia.colpo.org http://www.roma.colpo.org http://www.del-piero.oscurita.org http://www.prima.oscurita.org http://www.porno.colpo.org http://www.serie-a.oscurita.org http://www.cavalli.elogio.org http://www.alitalia.oscurita.org http://www.2006-calendario.elogio.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

SQL Server 2005 XML feature will solve your problem. Go to read articale at
http://msdn.microsoft.com/XML/Buildi.../sql2k5xml.asp.
And get a SQL Server 2005 to play with.

"http://www.fare.elogio.org" wrote:

>
> > Hello.
> >
> > I'm pretty new to SQL Server (switching from Visual FoxPro).
> >
> > I'm developing a database that will store, amongst other things, names
> > and addresses of people from all over the world (approx 100,000
> > records). Up to now, I've always used a flat model of storing address
> > data using fields:
> >
> > Address line 1
> > Address line 2
> > Town/City
> > County/State
> > Postcode/ZIP
> > Country
> >
> > But this model doesn't cater very well for the intricacies of postal
> > systems all over the world. There is also redundancy, particularly with
> > fields Line2, County/State, Postcode/ZIP, which are not needed for many
> > countries.
> >
> > It also occurs to me that using multiple fields like this in a table
> > with several fields gives no indication of the linked nature of the
> > information. For example, if the town field is changed, then chances
> > are that some of the other lines of the address become obsolete unless
> > they are changed at the same time.
> >
> > So I have been thinking about storing the address in an XML structure
> > in a single field. This will allow me, where necessary, to break down
> > address data into smaller units where known (e.g. premise name, premise
> > number, apartment number), or if a breakdown is not known to store
> > chunks of data together. It also then allows me the flexibility to
> > re-build the data in an appropriate order (e.g. for an address label)
> > relevant for the particular country/language - for example number
> > before/after street; postcode before town/separate line at the end.
> >
> > An example fragment might be:
> >
> > <address>
> > <streetaddress>
> > <premisename>Oak Cottage</premisename>
> > <premisenumber>15</premisenumber>
> > <streetname>Greenhill</streetname>
> > <streettype>Crescent</streettype>
> > </streetaddress>
> > <locality>
> > <village>Little Hampton</village>
> > <town>Rickmansworth</town>
> > <county>Hertfordshire</county>
> > </locality>
> > <postcode>WD3 3UH</postcode>
> > <country>UK</country>
> > </address>
> >
> >
> > Bearing in mind some of this data will need to be searchable
> > (especially postcode, country, and possibly town), are the possible
> > benefits of this going to be outweighed by vast amounts of extra work,
> > and a performance penalty, or does it sound like a good practical use
> > of the XML data type?
> >
> > Any comments gratefully appreciated.
> >
> > Ben
>
> http://www.fare.elogio.org http://www.formula-1.colpo.org http://www.pornografia.oscurita.org http://www.venezia.elogio.org http://www.jesse-mccartney.elogio.org http://www.teatro.colpo.org http://www.egipto.elogio.org http://www.palermo.oscurita.org http://www.sex.colpo.org http://www.sardinia.colpo.org http://www.roma.colpo.org http://www.del-piero.oscurita.org http://www.prima.oscurita.org http://www.porno.colpo.org http://www.serie-a.oscurita.org http://www.cavalli.elogio.org http://www.alitalia.oscurita.org http://www.2006-calendario.elogio.org
>
> Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.
>