Person.vAdditionalContactInfo View

Description

Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
ContactID	int	4
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
TelephoneNumber	nvarchar	50
TelephoneSpecialInstructions	nvarchar	16
Street	nvarchar	50
City	nvarchar	50
StateProvince	nvarchar	50
PostalCode	nvarchar	50
CountryRegion	nvarchar	50
HomeAddressSpecialInstructions	nvarchar	16
EMailAddress	nvarchar	128
EMailSpecialInstructions	nvarchar	16
EMailTelephoneNumber	nvarchar	50
rowguid	uniqueidentifier	16
ModifiedDate	datetime	4

Objects that Person.vAdditionalContactInfo depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2

View Definition

CREATE VIEW [Person].[vAdditionalContactInfo] 
AS 

SELECT 
[ContactID] 
,[FirstName]
,[MiddleName]
,[LastName]
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber] 
,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
(act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress] 
,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions] 
,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
(act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber] 
,[rowguid] 
,[ModifiedDate]
FROM [Person].[Contact]
OUTER APPLY [AdditionalContactInfo].nodes(
'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
/ci:AdditionalContactInfo') AS ContactInfo(ref) 
WHERE [AdditionalContactInfo] IS NOT NULL;

Related Objects

Person Schema
AdventureWorks Database