About the Individual.Demographics xml Column
The AdventureWorks database includes two kinds of customers: individuals who purchase directly from Adventure Works 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 Individual 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 AdventureWorks IndividualSurvey XML schema.
This is a sample XML instance stored in the Individual.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:
SELECT Demographics FROM Sales.Individual