join xml structure with table in sql server 2005

N

NickBF

New Member
\[code\]<C1003Data xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <LTV>-1</LTV> <CLTV>-1</CLTV> <bIncludeSpouse>false</bIncludeSpouse> <bNotIncludeSpouse>false</bNotIncludeSpouse> <BorrowerCreditScore> <Experian /> <TransUnion /> <Equifax /> </BorrowerCreditScore> <CoBorrowerCreditScore> <Experian /> <TransUnion /> <Equifax /> </CoBorrowerCreditScore> <SubFinancing> <FirstOrSecondMortgage>-1</FirstOrSecondMortgage> <OtherMortgage>-1</OtherMortgage> </SubFinancing> <MortgageType> <strLoanApplied>01</strLoanApplied> <dLoanAmount>-1</dLoanAmount> <dInterestRate>-1</dInterestRate> <nNumberofMonths>360</nNumberofMonths> <strAmortizationType>05</strAmortizationType> <dGPMRate>-1</dGPMRate> <strLoanPosition>1</strLoanPosition> <dPurchasePrice>-1</dPurchasePrice> <dAppraisedValue>-1</dAppraisedValue> <dTotalLoanAmt>-1</dTotalLoanAmt> <dMIPorFF>-1</dMIPorFF> <dQualRate>-1</dQualRate> <nDueIn>360</nDueIn> <dMonthlyPmt>-1</dMonthlyPmt> </MortgageType> <PropertyInfo> <strAddress /> <strCity /> <strState /> <strZip /> <strCounty /> <Warrantable>true</Warrantable> <strLoanPurpose>16</strLoanPurpose> <dCashout>-1</dCashout> <strPropertyWillBe>1</strPropertyWillBe> <dGrossRent>-1</dGrossRent> <dOccupRent>-1</dOccupRent> <nPeriod>-1</nPeriod> <dConstructionYearLotAcquired>-1</dConstructionYearLotAcquired> <dConstructionAmountExistingLiens>-1</dConstructionAmountExistingLiens> <dConstructionCostofImprovements>-1</dConstructionCostofImprovements> <dConstructionOriginalCost>-1</dConstructionOriginalCost> <dConstructionValueofLot>-1</dConstructionValueofLot> <dConstructionTotalValue>-1</dConstructionTotalValue> <dRefinanceYearAcquired>-1</dRefinanceYearAcquired> <dRefinanceAmountExistingLiens>-1</dRefinanceAmountExistingLiens> <dRefinanceOriginalCost>-1</dRefinanceOriginalCost> <dRefinanceCostofImprovements>-1</dRefinanceCostofImprovements> </PropertyInfo> <BorrowerInfo> <strFirstName>MARTIN</strFirstName> <strMidName /> <strLastName>TESTCO</strLastName> <strSocSecNo>000-06-0914</strSocSecNo> <strHomePhone /> <strCell /> <strEmail /> <strDOB /> <strMaritalStatus>Married</strMaritalStatus> <strCitizenship>US Citizen</strCitizenship> <bPresentAddressOwnt>false</bPresentAddressOwnt> <bPresentAddressRent>false</bPresentAddressRent> <strPresentAddressStreet>4527 COWBOY LN</strPresentAddressStreet> <strPresentAddressCity>Santa Ana</strPresentAddressCity> <strPresentAddressState>CA</strPresentAddressState> <strPresentAddressZip>92702</strPresentAddressZip> <bMailingSameAsPresent>false</bMailingSameAsPresent> <bFormerAddressOwn>false</bFormerAddressOwn> <bFormerAddressRent>false</bFormerAddressRent> </BorrowerInfo> <bSameAsBorrower>false</bSameAsBorrower> <CoBorrowerInfo> <strFirstName /> <strMidName /> <strLastName /> <strSocSecNo /> <strHomePhone /> <strCell /> <strEmail /> <strDOB /> <strMaritalStatus>Married</strMaritalStatus> <strCitizenship>US Citizen</strCitizenship> <bPresentAddressOwnt>false</bPresentAddressOwnt> <bPresentAddressRent>false</bPresentAddressRent> <bMailingSameAsPresent>false</bMailingSameAsPresent> <bFormerAddressOwn>false</bFormerAddressOwn> <bFormerAddressRent>false</bFormerAddressRent> </CoBorrowerInfo> <BoEmploymentInfo> <bSelfEmployed>false</bSelfEmployed> <strWorkPhone /> </BoEmploymentInfo> <CoEmploymentInfo> <bSelfEmployed>false</bSelfEmployed> <strWorkPhone /> </CoEmploymentInfo> <BoFormerEmpInfo> <CFormerEmploymentInformation> <bSelfEmployed>false</bSelfEmployed> </CFormerEmploymentInformation> </BoFormerEmpInfo> <CoFormerEmpInfo> <CFormerEmploymentInformation> <bSelfEmployed>false</bSelfEmployed> </CFormerEmploymentInformation> </CoFormerEmpInfo> <SREO> <CSchedule> <dMarketValue>-1</dMarketValue> <dAmtOfMtg>-1</dAmtOfMtg> <dGrossRent>-1</dGrossRent> <dMtgPayment>-1</dMtgPayment> <dInsTaxMisc>-1</dInsTaxMisc> <dNetRent>-1</dNetRent> </CSchedule> </SREO> <GrossMonthlyIncome> <dBorrowerBaseIncome>-1</dBorrowerBaseIncome> <dBorrowerOvertime>-1</dBorrowerOvertime> <dBorrowerBonuses>-1</dBorrowerBonuses> <dBorrowerCommissions>-1</dBorrowerCommissions> <dBorrowerDividends>-1</dBorrowerDividends> <dBorrowerNetRentalIncome>-1</dBorrowerNetRentalIncome> <dBorrowerOtherIncome>-1</dBorrowerOtherIncome> <dBorrowerOtherIncome1>-1</dBorrowerOtherIncome1> <dBorrowerTotal>-1</dBorrowerTotal> <dCoBorrowerBaseIncome>-1</dCoBorrowerBaseIncome> <dCoBorrowerOvertime>-1</dCoBorrowerOvertime> <dCoBorrowerBonuses>-1</dCoBorrowerBonuses> <dCoBorrowerCommissions>-1</dCoBorrowerCommissions> <dCoBorrowerDividends>-1</dCoBorrowerDividends> <dCoBorrowerNetRentalIncome>-1</dCoBorrowerNetRentalIncome> <dCoBorrowerOtherIncome>-1</dCoBorrowerOtherIncome> <dCoBorrowerOtherIncome1>-1</dCoBorrowerOtherIncome1> <dCoBorrowerTotal>-1</dCoBorrowerTotal> <dTotalBaseIncome>-1</dTotalBaseIncome> <dTotalOvertime>-1</dTotalOvertime> <dTotalBonuses>-1</dTotalBonuses> <dTotalCommissions>-1</dTotalCommissions> <dTotalDividends>-1</dTotalDividends> <dTotalNetRentalIncome>-1</dTotalNetRentalIncome> <dTotalOtherIncome>-1</dTotalOtherIncome> <dTotalOtherIncome1>-1</dTotalOtherIncome1> <dTotalTotal>-1</dTotalTotal> <dOtherIncome1>-1</dOtherIncome1> <dOtherIncome2>-1</dOtherIncome2> <dOtherIncome3>-1</dOtherIncome3> <dPresentRent>-1</dPresentRent> <dPresentFirsMortgage>-1</dPresentFirsMortgage> <dPresentOtherFinancing>-1</dPresentOtherFinancing> <dPresentHazardInsurance>-1</dPresentHazardInsurance> <dPresentRealEstateTaxes>-1</dPresentRealEstateTaxes> <dPresentMortgageInsurance>-1</dPresentMortgageInsurance> <dPresentHomeownerAssociationDues>-1</dPresentHomeownerAssociationDues> <dPresentOtherExpenses>-1</dPresentOtherExpenses> <dPresentTotal>-1</dPresentTotal> <dProposedFirsMortgage>-1</dProposedFirsMortgage> <dProposedOtherFinancing>-1</dProposedOtherFinancing> <dProposedHazardInsurance>-1</dProposedHazardInsurance> <dProposedRealEstateTaxes>-1</dProposedRealEstateTaxes> <dProposedMortgageInsurance>-1</dProposedMortgageInsurance> <dProposedHomeownerAssociationDues>-1</dProposedHomeownerAssociationDues> <dProposedOtherExpenses>-1</dProposedOtherExpenses> <dProposedTotal>-1</dProposedTotal> </GrossMonthlyIncome> <ScheduleOfRealEstate> <bCurrentResidence>false</bCurrentResidence> <bBubjectProperty>false</bBubjectProperty> <dPresentMarketValue>-1</dPresentMarketValue> <dMortgageLiens>-1</dMortgageLiens> <dMortgagePayments>-1</dMortgagePayments> <dMaintTaxesIns>-1</dMaintTaxesIns> <dGrossRentalIncome>-1</dGrossRentalIncome> <dNetRentalIncome>-1</dNetRentalIncome> <bAddAnotherProperty>false</bAddAnotherProperty> <bCurrentResidence1>false</bCurrentResidence1> <bBubjectProperty1>false</bBubjectProperty1> <dPresentMarketValue1>-1</dPresentMarketValue1> <dMortgageLiens1>-1</dMortgageLiens1> <dMortgagePayments1>-1</dMortgagePayments1> <dMaintTaxesIns1>-1</dMaintTaxesIns1> <dGrossRentalIncome1>-1</dGrossRentalIncome1> <dNetRentalIncome1>-1</dNetRentalIncome1> <bAddAnotherProperty1>false</bAddAnotherProperty1> <bCurrentResidence2>false</bCurrentResidence2> <bBubjectProperty2>false</bBubjectProperty2> <dPresentMarketValue2>-1</dPresentMarketValue2> <dMortgageLiens2>-1</dMortgageLiens2> <dMortgagePayments2>-1</dMortgagePayments2> <dMaintTaxesIns2>-1</dMaintTaxesIns2> <dGrossRentalIncome2>-1</dGrossRentalIncome2> <dNetRentalIncome2>-1</dNetRentalIncome2> <bAddAnotherProperty2>false</bAddAnotherProperty2> <dTotalPresentMarketValue>-1</dTotalPresentMarketValue> <dTotalMortgageLiens>-1</dTotalMortgageLiens> <dTotalGrossRentalIncome>-1</dTotalGrossRentalIncome> <dRentalIncome>-1</dRentalIncome> <dTotalMortgagePayments>-1</dTotalMortgagePayments> <dTotalMaintTaxesIns>-1</dTotalMaintTaxesIns> <dTotalreportedNetRentalIncome>-1</dTotalreportedNetRentalIncome> <dEffectiveNetRentalIncome>-1</dEffectiveNetRentalIncome> </ScheduleOfRealEstate> <AssetsInfo> <bJointly>false</bJointly> <dAmountOfDeposit>-1</dAmountOfDeposit> <dAmountOfDeposit1>-1</dAmountOfDeposit1> <dTotalBankDeposits>-1</dTotalBankDeposits> <dMarketValueOfRealEstate>-1</dMarketValueOfRealEstate> <dVestedInterest>-1</dVestedInterest> <dNetWorthOfBusiness>-1</dNetWorthOfBusiness> <dLifeInsuranceFaceAmount>-1</dLifeInsuranceFaceAmount> <dLifeInsuranceMarketValue>-1</dLifeInsuranceMarketValue> <dSubtotalLiquidAssets>-1</dSubtotalLiquidAssets> <dAutomobileOwned1>-1</dAutomobileOwned1> <dAutomobileOwned2>-1</dAutomobileOwned2> <dAutomobileOwned3>-1</dAutomobileOwned3> <dStocksAndBond1>-1</dStocksAndBond1> <dStocksAndBond2>-1</dStocksAndBond2> <dStocksAndBond3>-1</dStocksAndBond3> <dOtherAssest1>-1</dOtherAssest1> <dOtherAssest2>-1</dOtherAssest2> <dOtherAssest3>-1</dOtherAssest3> <dOtherAssest4>-1</dOtherAssest4> <dTotalAssest>-1</dTotalAssest> <dAlimony>-1</dAlimony> <dJobRelated1>-1</dJobRelated1> <dJobRelated2>-1</dJobRelated2> <dNetWorth>0</dNetWorth> <dBalanceTotal>-1</dBalanceTotal> <dPaymentTotal>-1</dPaymentTotal> <dMarketValue>-1</dMarketValue> <dMtgAmount>-1</dMtgAmount> <dCrossRentInc>-1</dCrossRentInc> <dMtgPayment>-1</dMtgPayment> <dInsTaxExp>-1</dInsTaxExp> <dNetRent>-1</dNetRent> <dAccountNumber1>-1</dAccountNumber1> <dAccountNumber2>-1</dAccountNumber2> </AssetsInfo> <BankDeposite> <CBankDeposite> <dBankValue>-1</dBankValue> </CBankDeposite> </BankDeposite> <Liabilities> <CLiabilities> <bResubordinated>false</bResubordinated> <bOmitted>false</bOmitted> <dLiabilitiesBalance>-1</dLiabilitiesBalance> <dLiabilitiesPayment>-1</dLiabilitiesPayment> <dLiabilitiesMosLeft>-1</dLiabilitiesMosLeft> <bLiabilitiesPaidOff>false</bLiabilitiesPaidOff> </CLiabilities> </Liabilities> <TransactionDetails> <dPurchasePrice>-1</dPurchasePrice> <dAlterations>-1</dAlterations> <dLand>-1</dLand> <dRefinance>-1</dRefinance> <dPrepaidItems>-1</dPrepaidItems> <dClosingCosts>-1</dClosingCosts> <dPMIandMIP>-1</dPMIandMIP> <dDiscount>-1</dDiscount> <dTotalCosts>-1</dTotalCosts> <dSubordinateFinancing>-1</dSubordinateFinancing> <dPaidBySeller>-1</dPaidBySeller> <dOtherCredits>-1</dOtherCredits> <dOtherCredits1>-1</dOtherCredits1> <dOtherCredits2>-1</dOtherCredits2> <dOtherCredits3>-1</dOtherCredits3> <dClosingCost>-1</dClosingCost> <dLoanAmount>-1</dLoanAmount> <dPMIandMIPFinanced>-1</dPMIandMIPFinanced> <dLoanAmountAutomatic>-1</dLoanAmountAutomatic> <dCashFromToBorrower>0</dCashFromToBorrower> </TransactionDetails> <Declarations /> <MonitoringPurposes> <bBorrowerFurnish>false</bBorrowerFurnish> <bCoBorrowerFurnish>false</bCoBorrowerFurnish> </MonitoringPurposes> <Other> <TSBalloon>false</TSBalloon> <TSBiweekly>false</TSBiweekly> <TSCommunityLending>false</TSCommunityLending> </Other> <RequestForAppraisal> <Appraiser /> <Lender /> <From /> <EscrowCompany /> <TitleCompany /> <ListingAgent /> <SellingAgent /> <AppraisalType> <Full>false</Full> <ExteriorOnly>false</ExteriorOnly> <MarketRentAnalysis>false</MarketRentAnalysis> <LandAppraisal>false</LandAppraisal> </AppraisalType> <EstimateOfValue> <AsIs>false</AsIs> <AsCompleted>false</AsCompleted> </EstimateOfValue> <FannieMae> <FM1004>false</FM1004> <FM1004C>false</FM1004C> <FM1004D>false</FM1004D> <FM1007>false</FM1007> <FM1025>false</FM1025> <FM1073>false</FM1073> <FM1075>false</FM1075> <FM2000>false</FM2000> <FM2000A>false</FM2000A> <FM2055>false</FM2055> <FM216>false</FM216> <FM2075>false</FM2075> <FM2090>false</FM2090> <FM2095>false</FM2095> <FMNA>false</FMNA> </FannieMae> <VeteransAdministration> <VA1805>false</VA1805> <VA8712>false</VA8712> </VeteransAdministration> <AppraisalCost>-1</AppraisalCost> <PaymentMethod> <COD>true</COD> <CreditCard>false</CreditCard> <InvoiceClient>false</InvoiceClient> <Bill>false</Bill> <Other>false</Other> </PaymentMethod> </RequestForAppraisal> <RequestForTitle> <Seller /> <InsuredLenderMortgage /> <Attachment> <PriorTitlePolicy>false</PriorTitlePolicy> <WarrantyDeed>false</WarrantyDeed> <TitleInsRequirements>false</TitleInsRequirements> <Survey>false</Survey> <Contract>false</Contract> </Attachment> <MailAway>false</MailAway> </RequestForTitle> <RequestForHazard> <MinReplacementCost>-1</MinReplacementCost> </RequestForHazard> <SollenAdditionalInfo> <LockPrice>-1</LockPrice> <LockPeriod>21</LockPeriod> <PrepayPenalty>No</PrepayPenalty> <BorrowerEmp>W-2</BorrowerEmp> <IRSForm>Yes</IRSForm> </SollenAdditionalInfo> <FloodReport> <IsInFloodArea>false</IsInFloodArea> </FloodReport> </C1003Data>\[/code\]In this XML structure, I use the following query for select firstname and lastname:\[code\]SELECT a.b.value('(strFirstName)[1]', 'Varchar(50)') FirstName, a.b.value('(./strLastName)[1]', 'VARCHAR(8000)') LastNameFROM dbo.FormDataCROSS APPLY C1003.nodes('//BorrowerInfo') a(b)\[/code\]It executes correctly. Now I need to join this structure with other table which also has first name and last name. please help.
 
You must log in or register to reply here.
Top