Merge views with .nodes xml data type method
I have two views listed below that get attributes at there respective levels ‘//BHILeads/Lead/Contact’ and ‘//BHILeads/Lead/PropertyInterest'.What I would like to do is make one view, but I don’t know how.I looked at the examples in AdventureWorks and they all use target name spaces.Do I have to use target name spaces to merge these views?
ALTERVIEW [dbo].[Is_vwBHI_Contact]AS
SELECTIsLead_Id,
ContRef.value('@FirstName','varchar(max)')as FirstName,
ContRef.value('@LastName','varchar(max)')as LastName,
ContRef.value('@Email','varchar(max)')as Email,
ContRef.value('@Phone','varchar(max)')as Phone,
ContRef.value('@StreetAddress','varchar(max)')as StreetAddress,
ContRef.value('@City','varchar(max)')as City,
ContRef.value('@State','varchar(2)')as State,
ContRef.value('@PostalCode','varchar(max)')as PostalCode
FROMIntegration.dbo.[Is.Lead]
CROSSAPPLYIsLead_XmlWellFormed.nodes('//BHILeads/Lead/Contact')AS R(ContRef)
ALTERVIEW [dbo].[Is_vwBHI_PropertyInterest]AS
SELECTIsLead_Id,
PrpIntRef.value('@StateName','varchar(max)')as I_State,
PrpIntRef.value('@MarketName','varchar(max)')as I_City,
PrpIntRef.value('@BuilderName','varchar(max)')as Market,
PrpIntRef.value('@CommunityNumber','varchar(max)')as I_Comm_ID,
PrpIntRef.value('@CommunityName','varchar(max)')as I_Comm_Name,
PrpIntRef.value('@MasterCommunity','varchar(max)')as I_Comm_Name2,
PrpIntRef.value('@PlanNumber','varchar(max)')as FP_ID,
PrpIntRef.value('@PlanName','varchar(max)')as FP_Name,
PrpIntRef.value('@Options','varchar(max)')as Comment3,
PrpIntRef.value('@SpecNumber','varchar(max)')as Comment4,
PrpIntRef.value('@SpecAddress','int')as Comment5,
PrpIntRef.value('@Price','decimal(18, 0)')as Price
FROMIntegration.dbo.[Is.Lead]
CROSSAPPLYIsLead_XmlWellFormed.nodes('//BHILeads/Lead/PropertyInterest')AS T(PrpIntRef)
