I have a setup as follows:
TableA - ID, PolicyReference, PolicyNumber
TableB - ID, Drivers, DOB, Sex
A record is inserted into TableA that has a UNIQUE ID, the Policy Reference and the Policy Number of that policy.
Each 'Policy' can then have multiple drivers on that policy, in TableB the ID is UNIQUE and is the JOIN between TableA and TableB, now I have written my SELECT XML query perfectly fine up until this point because when I don't include TableB it returns 1 row for each record, however when I include TableB if there is more than 1 driver this messes it up.
Below is the sample XML I currenly return from 1 row:
<PCNewBusiness>
<PolicyNumber>999999999</PolicyNumber>
<StartDate>2016-10-17T00:00:002017-10-17T00:00:00</StartDate>
<Premium>
<NetAmount>100.00</NetAmount>
<IPT>10.00</IPT>
</Premium>
<Fee>
<NetAmount>50.00</NetAmount>
<IPT>0</IPT>
</Fee>
<Policyholder>
<PolicyholderType>
<PrivateIndividual>
<Name>
<Title>Mr</Title>
<FirstNames>Bob</FirstNames>
<LastName>Test</LastName>
</Name>
<DateOfBirth>1985-12-03T00:00:00</DateOfBirth>
<MaritalStatus>Single</MaritalStatus>
<Sex>Male</Sex>
</PrivateIndividual>
</PolicyholderType>
<Contact>Mr Bob Test</Contact>
<Address>
<Address1>This</Address1>
<Address2>Test</Address2>
<Address3>Address</Address3>
<Address4>Is Fake</Address4>
<Postcode>BB2 1AB</Postcode>
</Address>
<Phone>07777777777</Phone>
<Email>mytestaccount@gmail.com</Email>
</Policyholder>
<Cover>
<CoverType>Comprehensive</CoverType>
<PolicyExcess>600</PolicyExcess>
<NoClaimsBonusYears>9</NoClaimsBonusYears>
<NCBOtherVehicle>0</NCBOtherVehicle>
<Use>Class1</Use>
</Cover>
Now when I join TableB it returns the the above information but twice, I know this is because the join on the ID recognised there is 2 rows returned in TableB because there are 2 drivers.
This would be my EXPECTED output when joining TableB.
<Cover>
<CoverType>Comprehensive</CoverType>
<PolicyExcess>600</PolicyExcess>
<NoClaimsBonusYears>9</NoClaimsBonusYears>
<NCBOtherVehicle>0</NCBOtherVehicle>
<Use>Class1</Use>
</Cover>
<Drivers>
<Driver>
<RelationshipToPolicyholder>Policyholder</RelationshipToPolicyholder>
<Name>
<Title>Mr</Title>
<FirstNames>Bob</FirstNames>
<LastName>Test</LastName>
</Name>
<DateOfBirth>1972-07-14</DateOfBirth>
<LicenceType>FullUK</LicenceType>
<PeriodLicenceHeld>MoreThanFiveYears</PeriodLicenceHeld>
<HomeAddress>
<Address1>Another Test</Address1>
<Address2>Address</Address2>
<Address3>Thats Fake</Address3>
<Postcode>AA1 1ZZ</Postcode>
</HomeAddress>
<PeriodResident>MoreThanFiveYears</PeriodResident>
<VehicleUseFrequency>Daily</VehicleUseFrequency>
<HasUseOfOtherVehicle>false</HasUseOfOtherVehicle>
<MainOccupation>Software Engineer</MainOccupation>
<HasCriminalConvictions>1</HasCriminalConvictions>
</Driver>
<Driver>
<RelationshipToPolicyholder>Spouse</RelationshipToPolicyholder>
<Name>
<Title>Mrs</Title>
<FirstNames>Jill</FirstNames>
<LastName>Test</LastName>
</Name>
<DateOfBirth>1972-07-14</DateOfBirth>
<LicenceType>FullUK</LicenceType>
<PeriodLicenceHeld>MoreThanFiveYears</PeriodLicenceHeld>
<HomeAddress>
<Address1>Another</Address1>
<Address2>Test</Address2>
<Address3>Address</Address3>
<Postcode>BB1 1BZ</Postcode>
</HomeAddress>
<PeriodResident>MoreThanFiveYears</PeriodResident>
<VehicleUseFrequency>Daily</VehicleUseFrequency>
<HasUseOfOtherVehicle>false</HasUseOfOtherVehicle>
<MainOccupation>Housewife</MainOccupation>
<HasCriminalConvictions>1</HasCriminalConvictions>
</Driver>
</Drivers>
So essentially I want to return 1 row of data but when I join TableB I need to use the data from the 2 rows in TableB to populate everything between the 'Drivers' Element.
Anymore information needed please fire away.
UPDATE
My present SELECT query as requested.
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL
) ) AS "PolicyRecordNumber",
sub.Polno AS "PCNewBusiness/PolicyNumber",
sub.[Inception Date] AS "PCNewBusiness/StartDate",
sub.[Renewal Date] AS "PCNewBusiness/StartDate",
sub.PremXIPT AS "PCNewBusiness/Premium/NetAmount",
sub.PremIPT AS "PCNewBusiness/Premium/IPT",
sub.FeeXIPT AS "PCNewBusiness/Fee/NetAmount",
sub.FeeIPT AS "PCNewBusiness/Fee/IPT",
ph.Title AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/Title",
ph.Firstname AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/FirstNames",
ph.Surname AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Name/LastName",
ph.DateOfBirth AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/DateOfBirth",
ph.MaritalStatus AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/MaritalStatus",
ph.Sex AS "PCNewBusiness/Policyholder/PolicyholderType/PrivateIndividual/Sex" ,
ph.Contact AS "PCNewBusiness/Policyholder/Contact",
ph.Addr1 AS "PCNewBusiness/Policyholder/Address/Address1",
ph.Addr2 AS "PCNewBusiness/Policyholder/Address/Address2",
ph.Addr3 AS "PCNewBusiness/Policyholder/Address/Address3",
ph.Addr4 AS "PCNewBusiness/Policyholder/Address/Address4",
ph.Pcode AS "PCNewBusiness/Policyholder/Address/Postcode",
ISNULL(ph.Tel,ph.Tel2) AS "PCNewBusiness/Policyholder/Phone",
ph.Email AS "PCNewBusiness/Policyholder/Email",
cov.Cover AS "PCNewBusiness/Cover/CoverType",
cov.Excess AS "PCNewBusiness/Cover/PolicyExcess",
cov.NCBYears AS "PCNewBusiness/Cover/NoClaimsBonusYears",
cov.NCBOtherVeh AS "PCNewBusiness/Cover/NCBOtherVehicle",
cov.ClassOfUse AS "PCNewBusiness/Cover/Use",
--dri.RelationshipToPH AS "PCNewBusiness/Drivers/Driver/RelationshipToPolicyHolder",
--dri.Drivertitle AS "PCNewBusiness/Drivers/Driver/Title" ,
--dri.FirstName AS "PCNewBusiness/Drivers/Driver/FirstNames" ,
--dri.Surname AS "PCNewBusiness/Drivers/Driver/LastName"
veh.Make AS "PCNewBusiness/Vehicles/Vehicle/Make",
veh.Model AS "PCNewBusiness/Vehicles/Vehicle/Model",
veh.Reg AS "PCNewBusiness/Vehicles/Vehicle/RegistrationNumber",
veh.Body AS "PCNewBusiness/Vehicles/Vehicle/BodyType",
veh.Parking AS "PCNewBusiness/Vehicles/Vehicle/LocationWhenNotInUse",
'' AS "PCNewBusiness/Vehicles/Vehicle/AnnualMileage",
veh.YearMade AS "PCNewBusiness/Vehicles/Vehicle/YearOfManufacture",
veh.Engine_size AS "PCNewBusiness/Vehicles/Vehicle/EngineSizeCc",
veh.Purchasedate AS "PCNewBusiness/Vehicles/Vehicle/PurchaseDate",
veh.Value AS "PCNewBusiness/Vehicles/Vehicle/PurchasePrice",
veh.Value1 AS "PCNewBusiness/Vehicles/Vehicle/EstimatedValue",
veh.Seats AS "PCNewBusiness/Vehicles/Vehicle/NumberOfSeats",
veh.RightHandDrive AS "PCNewBusiness/Vehicles/Vehicle/RightHandDrive",
veh.Fuel AS "PCNewBusiness/Vehicles/Vehicle/FuelType"
FROM dbo.Submissions sub
LEFT OUTER JOIN dbo.PolicyHolder ph ON ph.ID = sub.ID
LEFT OUTER JOIN dbo.Cover cov ON cov.ID = sub.ID
--LEFT OUTER JOIN dbo.Drivers dri ON dri.ID = sub.ID
LEFT OUTER JOIN dbo.Vehicle veh ON veh.ID = sub.ID
--LEFT OUTER JOIN dbo.Convictions con ON con.ID = sub.ID
--LEFT OUTER JOIN dbo.Claims cla ON cla.ID = sub.ID
--LEFT OUTER JOIN dbo.Medical med ON med.ID = sub.ID
WHERE sub.[Transaction] = 'New Business' AND sub.[Ledger Date] >= dbo.Today()-7
FOR XML PATH('Policy')
UPDATE 2
OK I have progress I entered in a sub-query for my 'Drivers' Select like below:
cov.Cover AS "PCNewBusiness/Cover/CoverType" ,
cov.Excess AS "PCNewBusiness/Cover/PolicyExcess" ,
cov.NCBYears AS "PCNewBusiness/Cover/NoClaimsBonusYears" ,
cov.NCBOtherVeh AS "PCNewBusiness/Cover/NCBOtherVehicle" ,
cov.ClassOfUse AS "PCNewBusiness/Cover/Use" ,
( SELECT dri1.ID ,
RelationshipToPH ,
dri1.FirstName ,
dri1.Surname
FROM dbo.Drivers dri1
INNER JOIN dbo.Submissions sub1 ON sub1.ID = dri1.ID
FOR
XML PATH('Driver') ,
TYPE
) AS "PCNewBusiness/Drivers"
This returns the Drivers in the place I need them to HOWEVER, the subquery is pulling through EVERY row in the 'Drivers' table instead of the relevant rows for each driver for that UNIQUE ID.
See below:
<Cover>
<CoverType>Comprehensive</CoverType>
<PolicyExcess>600</PolicyExcess>
<NoClaimsBonusYears>4</NoClaimsBonusYears>
<NCBOtherVehicle>0</NCBOtherVehicle>
<Use>Class1</Use>
</Cover>
<Drivers>
<Driver>
<ID>1</ID>
<RelationshipToPH>Policyholder</RelationshipToPH>
<FirstName>John</FirstName>
<Surname>Doe</Surname>
</Driver>
<Driver>
<ID>2</ID>
<RelationshipToPH>Commonlaw Spouse</RelationshipToPH>
<FirstName>Bob</FirstName>
<Surname>Test</Surname>
</Driver>
<Driver>
<ID>2</ID>
<RelationshipToPH>Policyholder</RelationshipToPH>
<FirstName>Jill</FirstName>
<Surname>Test</Surname>
</Driver>
<Driver>
<ID>3</ID>
<RelationshipToPH>Policyholder</RelationshipToPH>
<FirstName>Jane</FirstName>
<Surname>Gowe</Surname>
</Driver>
<Driver>
<ID>4</ID>
<RelationshipToPH>Policyholder</RelationshipToPH>
<FirstName>Dave</FirstName>
<Surname>Grace</Surname>
</Driver>
My first row should only return the 2 Drivers with ID "2" for example.
your own update 2 was exactly the thing I wanted to suggest you...
Now I think the rest is easy:
Change the JOIN of your sub-select to a simple WHERE:
(SELECT dri1.ID ,
RelationshipToPH ,
dri1.FirstName ,
dri1.Surname
FROM dbo.Drivers dri
WHERE dri.ID=sub.ID
FOR
XML PATH('Driver') ,
TYPE
) AS "PCNewBusiness/Drivers",