Person.vAdditionalContactInfo (view) - Revision history

Bot at 22:16, 23 June 2010

2010-06-23T22:16:24Z

Bot at 22:07, 2 February 2010

2010-02-02T22:07:41Z

Bot at 21:56, 28 December 2009

2009-12-28T21:56:54Z

Bot at 06:00, 30 November 2009

2009-11-30T06:00:48Z

Bot at 05:39, 30 November 2009

2009-11-30T05:39:00Z

Bot at 05:25, 30 November 2009

2009-11-30T05:25:27Z

Bot at 22:59, 26 November 2009

2009-11-26T22:59:16Z

Bot at 22:28, 26 November 2009

2009-11-26T22:28:25Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Person.vAdditionalContactInfo |- |}

 CREATE VIEW [Person].[vAdditionalCon...

2009-01-03T12:36:25Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Person.vAdditionalContactInfo |- |} <pre> CREATE VIEW [Person].[vAdditionalCon...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''view
| Person.vAdditionalContactInfo
|-
|}

<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>

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''View
 | Person.vAdditionalContactInfo
@@ Line 8: / Line 8: @@
 | '''Description
 | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
 |}
 === Source ===
@@ Line 70: / Line 68: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table
 | [[Person.Contact_(table)|Person.Contact]]
-|-
+|- valign="top"
 | Schema
 | Schema

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''view
+| '''View
 | Person.vAdditionalContactInfo
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE VIEW [Person].[vAdditionalContactInfo]
@@ Line 61: / Line 63: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 72: / Line 74: @@
 | Table
 | [[Person.Contact_(table)|Person.Contact]]
 |}

@@ Line 64: / Line 64: @@
 | Person.vAdditionalContactInfo
 |- valign="top"
 | '''Description
 | Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
 |-
 |}

@@ Line 125: / Line 125: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
 | '''Referenced Object
 |-
 | [[Person.Contact_(table)|Person.Contact]]
-| Table
 |-
 | Schema
 | Schema
 |}

@@ Line 63: / Line 63: @@
 | '''view
 | Person.vAdditionalContactInfo
-|-
+|- valign="top"
 | '''Description
@@ Line 128: / Line 128: @@
 | '''Object Type
 | '''Dependency Type
-−
 |-
 | [[Person.Contact_(table)|Person.Contact]]
 | Table
 | Select
 |}

@@ Line 5: / Line 5: @@
 | '''view
 | Person.vAdditionalContactInfo
 |-
 |}
@@ Line 57: / Line 60: @@
 WHERE [AdditionalContactInfo] IS NOT NULL;
 </pre>
 == automatically generated ==