About the Individual.Demographics xml Column
The AdventureWorks2008R2 database includes two kinds of customers: individuals who purchase directly from Adventure Works Cycles and resellers. Data for resellers is stored in the Demographics column of the Store table. This is discussed in the topic, About the Store.Demographics xml Column.
The usual demographic information for individual customers, such as marital status, number of children, education, occupation, number of cars owned, and hobbies, is stored in the Demographics column of type XML in the Person table.
In a typical survey, customers do not answer all the questions. Therefore, instead of creating several columns, one for each question in the survey, and storing NULL values in the database for unanswered questions, a single column of type XML is used.
The survey information stored as XML can also be provided to customer relations management systems and business intelligence systems.
This is a typed xml column. The schemas used for this column can be viewed at this Microsoft Web site. This column uses the AdventureWorks2008R2 IndividualSurvey XML schema.
This is a sample XML instance stored in the Person.Demographics column:
<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> <TotalPurchaseYTD>8248.99</TotalPurchaseYTD> <DateFirstPurchase>2001-07-22Z</DateFirstPurchase> <BirthDate>1966-04-08Z</BirthDate> <MaritalStatus>M</MaritalStatus> <YearlyIncome>75001-100000</YearlyIncome> <Gender>M</Gender> <TotalChildren>2</TotalChildren> <NumberChildrenAtHome>0</NumberChildrenAtHome> <Education>Bachelors </Education> <Occupation>Professional</Occupation> <HomeOwnerFlag>1</HomeOwnerFlag> <NumberCarsOwned>0</NumberCarsOwned> <Hobby>Golf</Hobby> <Hobby>Watch TV</Hobby> <CommuteDistance>1-2 Miles</CommuteDistance> </IndividualSurvey>
Run the following query to find more XML instances:
USE AdventureWorks2008R2; GO SELECT Demographics FROM Person.Person WHERE Demographics IS NOT NULL;