Difference between revisions of "Person.vAdditionalContactInfo (view)"
Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
− | |- | + | |- valign="top" |
| '''View | | '''View | ||
| Person.vAdditionalContactInfo | | Person.vAdditionalContactInfo | ||
Line 8: | Line 8: | ||
| '''Description | | '''Description | ||
| Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. | | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. | ||
− | |||
|} | |} | ||
− | |||
=== Source === | === Source === | ||
Line 70: | Line 68: | ||
| '''Referenced Object | | '''Referenced Object | ||
− | |- | + | |- valign="top" |
| Select | | Select | ||
| Table | | Table | ||
| [[Person.Contact_(table)|Person.Contact]] | | [[Person.Contact_(table)|Person.Contact]] | ||
− | |- | + | |- valign="top" |
| Schema | | Schema | ||
| Schema | | Schema |
Latest revision as of 23:16, 23 June 2010
wikibot[edit]
View | Person.vAdditionalContactInfo |
Description | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. |
Source[edit]
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;
References[edit]
Dependency Type | Object Type | Referenced Object |
Select | Table | Person.Contact |
Schema | Schema | Person |
automatically generated[edit]
view | Person.vAdditionalContactInfo |
Description | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. |
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;
Dependency Type | Object Type | Referenced Object |
Select | Table | Person.Contact |
Schema | Schema | Person |