Person.vAdditionalContactInfo

[Person].[vAdditionalContactInfo]

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

Property	Value
Collation	SQL_Latin1_General_CP1_CI_AS
ANSI Nulls On	True
Quoted Identifier On	True
Created	13:14:55 14 marca 2012
Last Modified	13:14:55 14 marca 2012

Columns

Name	Data Type	Max Length (Bytes)
BusinessEntityID	int	4
FirstName	[dbo].[Name]	100
MiddleName	[dbo].[Name]	100
LastName	[dbo].[Name]	100
TelephoneNumber	nvarchar(50)	100
TelephoneSpecialInstructions	nvarchar(max)	max
Street	nvarchar(50)	100
City	nvarchar(50)	100
StateProvince	nvarchar(50)	100
PostalCode	nvarchar(50)	100
CountryRegion	nvarchar(50)	100
HomeAddressSpecialInstructions	nvarchar(max)	max
EMailAddress	nvarchar(128)	256
EMailSpecialInstructions	nvarchar(max)	max
EMailTelephoneNumber	nvarchar(50)	100
rowguid	uniqueidentifier	16
ModifiedDate	datetime	8

SQL Script

CREATE VIEW [Person].[vAdditionalContactInfo]
AS
SELECT
   [BusinessEntityID]
   ,[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].[Person]
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;
GO
EXEC sp_addextendedproperty N'MS_Description', N'Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.', 'SCHEMA', N'Person', 'VIEW', N'vAdditionalContactInfo', NULL, NULL
GO

Uses