Latest revision |
Your text |
Line 1: |
Line 1: |
| == wikibot == | | == wikibot == |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- valign="top"
| |
− | | '''View
| |
− | | Person.vAdditionalContactInfo
| |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
| |
− | |}
| |
− |
| |
− | === Source ===
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | === References ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Person.Contact_(table)|Person.Contact]]
| |
− |
| |
− | |- valign="top"
| |
− | | Schema
| |
− | | Schema
| |
− | | [[Person_(schema)|Person]]
| |
− |
| |
− | |}
| |
− |
| |
− |
| |
− | == automatically generated ==
| |
| | | |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
Line 87: |
Line 5: |
| | '''view | | | '''view |
| | Person.vAdditionalContactInfo | | | Person.vAdditionalContactInfo |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
| |
| |- | | |- |
| |} | | |} |
| | | |
| <pre> | | <pre> |
− |
| |
| CREATE VIEW [Person].[vAdditionalContactInfo] | | CREATE VIEW [Person].[vAdditionalContactInfo] |
| AS | | AS |
Line 143: |
Line 57: |
| WHERE [AdditionalContactInfo] IS NOT NULL; | | WHERE [AdditionalContactInfo] IS NOT NULL; |
| </pre> | | </pre> |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[Person.Contact_(table)|Person.Contact]]
| |
− |
| |
− | |-
| |
− | | Schema
| |
− | | Schema
| |
− | | [[Person_(schema)|Person]]
| |
− |
| |
− | |}
| |