XML and Sql Server 2005

Question

Hi, i have a problem that I want to resolve in a best possible way. The thing is that I made a schema that looks like this:

print("

<xs:complexType name="rentACarT">
 <xs:sequence>
  <xs:element name="poslovnice" type="poslovniceT" />
  <xs:element name="korisnici" type="korisniciT" />
 </xs:sequence>
</xs:complexType>

<xs:complexType name="poslovniceT">
 <xs:sequence>
  <xs:element name="poslovnica" type="poslovnicaT" minOccurs="0" maxOccurs="unbounded" />
 </xs:sequence>
</xs:complexType>

<xs:complexType name="poslovnicaT">
 <xs:sequence>
  <xs:element name="naziv" type="xs:string" />
  <xs:element name="adresa" type="adresaT" />
  <xs:element name="grad" type="gradT" />
  <xs:element name="vozila" type="vozilaT" />
  <xs:element name="zaposlenici" type="zaposleniciT" />
  <xs:element name="posudbe" type="posudbeT" />
 </xs:sequence>
 <xs:attribute name="id" type="xs:int" />
</xs:complexType>

<xs:complexType name="vozilaT">
 <xs:sequence>
  <xs:element name="vozilo" type="voziloT" minOccurs="0" maxOccurs="unbounded"/>
 </xs:sequence>
</xs:complexType>

<xs:complexType name="zaposleniciT">
 <xs:sequence>
  <xs:element name="zaposlenik" type="zaposlenikT" minOccurs="0" maxOccurs="unbounded"/>
 </xs:sequence>
</xs:complexType>

<xs:complexType name="korisniciT">
 <xs:sequence>
  <xs:element name="korisnik" type="korisnikT" minOccurs="0" maxOccurs="unbounded"/>
 </xs:sequence>
</xs:complexType>

<xs:complexType name="posudbeT">
 <xs:sequence>
  <xs:element name="posudba" type="posudbaT" minOccurs="0" maxOccurs="unbounded"/>
 </xs:sequence>
</xs:complexType>


<xs:complexType name="posudbaT">
 <xs:sequence>
  <xs:element name="idVozila" type="xs:int" />
  <xs:element name="jmbgZaposlenika" type="jmbgT" />
  <xs:element name="jmbgKorisnika" type="jmbgT" />
  <xs:element name="datumPosudbe" type="xs:date" />
  <xs:element name="ugovoreniDatumPovratka" type="xs:date" />
  <xs:element name="stvarniDatumPovratka" type="xs:date" />
 </xs:sequence>
 <xs:attribute name="id" type="xs:integer" />
</xs:complexType>

<xs:complexType name="voziloT">
 <xs:sequence>
  <xs:element name="registracija" type="xs:string" />
  <xs:element name="modelVozila" type="modelVozilaT" />
  <xs:element name="godinaProizvodnje" type="godinaT" />
  <xs:element name="cijenaPosudbePoDanu" type="xs:double" />
 </xs:sequence>
 <xs:attribute name="id" type="xs:int" />
</xs:complexType>

<xs:complexType name="zaposlenikT">
 <xs:sequence>
  <xs:element name="ime" type="xs:string" />
  <xs:element name="prezime" type="xs:string" />
  <xs:element name="spol" type="spolT" />
  <xs:element name="datumZaposlenja" type="xs:date" />
  <xs:element name="grad" type="xs:double" />
 </xs:sequence>
 <xs:attribute name="jmbg" type="jmbgT" />
</xs:complexType>

<xs:complexType name="korisnikT">
 <xs:sequence>
  <xs:element name="ime" type="xs:string" />
  <xs:element name="prezime" type="xs:string" />
  <xs:element name="spol" type="spolT" />
  <xs:element name="adresa" type="adresaT" />
  <xs:element name="grad" type="gradT" />
  <xs:element name="status" type="statusKorisnikaT" />
 </xs:sequence>
 <xs:attribute name="jmbg" type="jmbgT" />
</xs:complexType>

<xs:complexType name="modelVozilaT">
 <xs:sequence>
  <xs:element name="tipVozila" type="tipVozilaT" />
  <xs:element name="marka" type="xs:string" />
  <xs:element name="model" type="xs:string" />
 </xs:sequence>
</xs:complexType>

<xs:simpleType name="tipVozilaT">
 <xs:restriction base="xs:string">
  <xs:enumeration value="auto" />
  <xs:enumeration value="kombi" />
  <xs:enumeration value="kamion" />
  <xs:enumeration value="limuzina" />
 </xs:restriction>
</xs:simpleType>

<xs:simpleType name="godinaT">
 <xs:restriction base="xs:int">
  <xs:minInclusive value="1980" />
  <xs:maxInclusive value="2050" />
 </xs:restriction>
</xs:simpleType>

<xs:simpleType name="jmbgT">
 <xs:restriction base="xs:string">
  <xs:pattern value="([0-9]){13}" />
 </xs:restriction>
</xs:simpleType>

<xs:simpleType name="spolT">
 <xs:restriction base="xs:string">
  <xs:enumeration value="m" />
  <xs:enumeration value="f" />
 </xs:restriction>
</xs:simpleType>

<xs:simpleType name="statusKorisnikaT">
 <xs:restriction base="xs:string">
  <xs:enumeration value="stalni" />
  <xs:enumeration value="novi" />
 </xs:restriction>
</xs:simpleType>

<xs:complexType name="gradT">
 <xs:sequence>
  <xs:element name="postanskiBroj" type="xs:int" />
  <xs:element name="naziv" type="xs:string" />
 </xs:sequence>
</xs:complexType>

<xs:complexType name="adresaT">
 <xs:sequence>
  <xs:element name="ulica" type="xs:string" />
  <xs:element name="kucniBroj" type="xs:int" />
 </xs:sequence>
</xs:complexType>

");

Now, I don't want to put the entire xml document into one row. At least I think it is bad. I would like to move "poslovnice"(contains multiple "poslovnica") in one table and "vozila" in other... And is it better to hold each "poslovnica" in one row in table Poslovnica or have all elements "poslovnica" in one row? And what about uniqueness since SQL Server doesn't support unique xml data type, I've seen that you can use triggers and functions, is there any other way? Also, if I break the xml document, and have poslovnice in one table and their vozila in other, how to achieve to show that vozilo belongs to poslovnica, should I put id of poslovnica in each vozilo in xml or should I reference rows in SQL Server?

Or would you reccomend some other database?

Thank you very much, I am sorry if I wasn't clear enough :( !

Miroslav

Answer 1

well first you have to ask yourself why do you want it stored in XML in the db? this seems like a perfect scenario to have tables Poslovnica, Vozilo and Zaposlenik and have intermediate tables that hold info on which employee rented what car where.

i don't see a reason to store this in xml at all.

Answer 2

Hey. I don't see any reason also but the thing is I have to do it. For college project. To avoid complications we used Sedna database and didn't use schema at all.