Is this a good way to store XML data in SQL SERVER 2008?

Question

I'm doing a feasibility study for storing data in the following structure with SQL SERVER 2008:

-MYTABLE |ID|RECORDS|BRANCH_OFFICE|MONTH|YEAR

The RECORDS column has the XML data type and it will look like:

<visits>
  <visit id="000112233">
    <costumer>Mr. One Costumer</costumer>
    <date>2011-02-10</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112234">
    <costumer>Mr. Another Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01235</employee>
  </visit>
  <visit id="000112235">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112236">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-15</date>
    <employee>MAT01235</employee>
  </visit>
</visits>

I'm intending to query the xml column with xquery, is this a good way to store these data? If i want to get the list of visits occurring in 2011-10, adding a tag with the difference of days between the "date" tag and now, how could it be?

Answer 1

...is this a good way to store these data?

No, this is probably the worst way to store this data if you want to query it effectively. You need to study some database normalisation examples before you go any further with this

This is a very quick sketch up of a possible schema that could be used. Incomplete and untested but it should be clear how this maps to your XML fragment.

CREATE TABLE dbo.Customer
(
    CustomerId INT NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId ASC)

CREATE TABLE dbo.Employee
(
    EmployeeNumber VARCHAR(10) NOT NULL
    , Title VARCHAR(20)
    , FirstName VARCHAR(100)
    , LastName VARCHAR(100)
)

ALTER TABLE dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNumber ASC)

CREATE TABLE dbo.Visit
(
    VisitId INT NOT NULL
    , VisitDate DATETIME
    , CustomerId INT
    , EmployeeNumber VARCHAR(10)
)

ALTER TABLE dbo.Visit
ADD CONSTRAINT PK_Visit PRIMARY KEY (VisitId ASC)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Customer] FOREIGN KEY(CustomerId)
REFERENCES dbo.Customer (CustomerId)

ALTER TABLE dbo.Visit WITH CHECK ADD CONSTRAINT [FK_Visit_Employee] FOREIGN KEY(EmployeeNumber)
REFERENCES dbo.Employee (EmployeeNumber)