XML and Sql Server 2005

I have a problem that I want to solve in the best possible way. The thing is, I created a schematic 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 on one line. At least I think it's bad. I would like to move "poslovnica" (contains several "poslovnica") in one table and "lift" in others ... And is it better to keep each "poslovnica" in one row in the Poslovnica table or have all the "poslovnica" elements in the same row ? What about uniqueness, since SQL Server does not support a unique xml datatype, I saw that you can use triggers and functions, is there any other way? Also, if I break the xml document and get poslovnice in one table and pick them up in another, how do I get to show that vozilo belongs to poslovnica, should I put the poslovnica id in each drove in the xml, or should I refer to rows in SQL Server?

Or could you recommend any other database?

Thanks a lot, I'm sorry if I wasn't clear enough :(!

Miroslav

+1


source to share


2 answers


Well, first you have to ask yourself why you want it to be stored in XML in the db? this seems like a perfect scenario to have Poslovnica, Vozilo and Zaposlenik tables and have staging tables that contain information about which employee rented which car where.



I see no reason to store this in xml at all.

+1


source


Hey. I also see no reason, but I have to do it. For college. To avoid complications, we used the Sedna database and did not use schema at all.



0


source







All Articles