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?

• Nick Chammas: Use XML if your data spec is variable or loosely defined. That's the primary benefit of using XML in SQL Server. If it's a fixed schema, however, you are better off taking Mark's suggestion below and mapping your XML schema to tables.
• Jack Douglas: also posted on SO
• Jack Douglas: @Julio can you post your SO comment here as well please?

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)