Person.vAdditionalContactInfo View
In This Topic
Description
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
Properties
| Creation Date | 27/10/2017 14:33 |
| Is Schema Bound |  |
| Encrypted | |
| Ansi Nulls |  |
Columns
| | Column Name | Description | Datatype | Length | Allow Nulls | Default | Formula |
|---|
| | BusinessEntityID | | Integer | 4 | | | |
| | FirstName | | dbo.Name | 50 | | | |
| | MiddleName | | dbo.Name | 50 |  | | |
| | LastName | | dbo.Name | 50 | | | |
| | TelephoneNumber | | VarWChar | 50 | | | |
| | TelephoneSpecialInstructions | | LongVarWChar | 16 | | | |
| | Street | | VarWChar | 50 | | | |
| | City | | VarWChar | 50 | | | |
| | StateProvince | | VarWChar | 50 | | | |
| | PostalCode | | VarWChar | 50 | | | |
| | CountryRegion | | VarWChar | 50 | | | |
| | HomeAddressSpecialInstructions | | LongVarWChar | 16 | | | |
| | EMailAddress | | VarWChar | 128 | | | |
| | EMailSpecialInstructions | | LongVarWChar | 16 | | | |
| | EMailTelephoneNumber | | VarWChar | 50 | | | |
| | rowguid | | GUID | 16 | | | |
| | ModifiedDate | | DBTimeStamp | 4 | | | |
Objects that Person.vAdditionalContactInfo depends on
| | Database Object | Object Type | Description | Dep Level |
|---|
 | Person.BusinessEntity | Table | Source of the ID that connects vendors, customers, and employees with address and contact information. | 2 |
 | dbo.Name | User Defined Data Type | | 2 |
| dbo.NameStyle | User Defined Data Type | | 5 |
| Person.Person | Table | Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts. | 4 |
View Definition
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;
|
See Also