About the Contact.AdditionalContactInfo xml Column
The Contact table stores typical customer contact information such as name, telephone number, and email address. This table also has a column of xml type named AdditionalContactInfo.
Customers can have multiple phones, cell phones, emails, and pagers. Because the exact number of additional contact points might not be available at design time, it is not possible to know how many columns will be needed to store that information. Instead of creating several columns to contain that information and then have to store NULL values, you can create a single XML-type column to store XML documents that contains additional contact information for each customer.
In the AdventureWorks database, this column is named AdditionalContactInfo. This additional contact information may then be shared with other applications. For example, this information could be useful to a Customer Relations Management (CRM) application.
The CRM application might also store contact information in this column. This could include information such as the date a customer was contacted and the notes of the conversation.
This is a typed xml column. The schemas used for this column can be viewed at this Microsoft Web site. This column uses the following schemas:
- AdventureWorks ContactInfo Schema
- AdventureWorks ContactRecord Schema
- AdventureWorks ContactTypes Schema
Sample XML Instance Stored in this Column
Following is the sample XML instance:
<AdditionalContactInfo xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" xmlns:crm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
<act:telephoneNumber>
<act:number>111-111-1111</act:number>
<act:SpecialInstructions>
Call only after 5PM.
</act:SpecialInstructions>
</act:telephoneNumber>
Note that customer has second home at this address.
<act:homePostalAddress>
<act:Street>123 Oak</act:Street>
<act:City>Seattle</act:City>
<act:StateProvince>WA</act:StateProvince>
<act:PostalCode>777</act:PostalCode>
<act:CountryRegion>USA</act:CountryRegion>
<act:SpecialInstructions>If correspondance to the primary address
fail, try this one</act:SpecialInstructions>
</act:homePostalAddress>
Customer provided additional email address.
<act:eMail>
<act:eMailAddress>customer1@xyz.com</act:eMailAddress>
<act:SpecialInstructions>Dont send emails for urgent issues. Instead use
this emergency contact phone
<act:telephoneNumber>
<act:number>112-111-1111</act:number>
</act:telephoneNumber>.
</act:SpecialInstructions>
</act:eMail>
<crm:ContactRecord date="2001-06-02Z">This customer is interested in
puchasing a high end bicycles for his family. The customer
contacted Mike in sales.
</crm:ContactRecord>
</AdditionalContactInfo>
Run the following query to find more XML instances:
select AdditionalContactInfo
from Person.Contact