About the Contact.AdditionalContactInfo xml Column

The Person 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 AdventureWorks2008R2 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:

  • ContactInfo Schema

  • ContactRecord Schema

  • ContactTypes Schema

Sample XML Instance Stored in this Column

Following is the sample XML instance:

<AdditionalContactInfo xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" xmlns:crm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" xmlns:act="http://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:

USE AdventureWorks2008R2;
GO
SELECT AdditionalContactInfo
FROM   Person.Person
WHERE AdditionalContactInfo IS NOT NULL;

See Also

Concepts