SQL XML Multiple Elements Per Row
<p>I have a setup as follows:</p>

<pre><code>TableA - ID, PolicyReference, PolicyNumber

TableB - ID, Drivers, DOB, Sex
</code></pre>

<p>A record is inserted into TableA that has a UNIQUE ID, the Policy Reference and the Policy Number of that policy.</p>

<p>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.</p>

<p>Below is the sample XML I currenly return from 1 row:</p>

<pre><code>&lt;PCNewBusiness&gt;
&lt;PolicyNumber&gt;999999999&lt;/PolicyNumber&gt;
&lt;StartDate&gt;2016-10-17T00:00:002017-10-17T00:00:00&lt;/StartDate&gt;
&lt;Premium&gt;
  &lt;NetAmount&gt;100.00&lt;/NetAmount&gt;
  &lt;IPT&gt;10.00&lt;/IPT&gt;
&lt;/Premium&gt;
&lt;Fee&gt;
  &lt;NetAmount&gt;50.00&lt;/NetAmount&gt;
  &lt;IPT&gt;0&lt;/IPT&gt;
&lt;/Fee&gt;
&lt;Policyholder&gt;
  &lt;PolicyholderType&gt;
    &lt;PrivateIndividual&gt;
      &lt;Name&gt;
        &lt;Title&gt;Mr&lt;/Title&gt;
        &lt;FirstNames&gt;Bob&lt;/FirstNames&gt;
        &lt;LastName&gt;Test&lt;/LastName&gt;
      &lt;/Name&gt;
      &lt;DateOfBirth&gt;1985-12-03T00:00:00&lt;/DateOfBirth&gt;
      &lt;MaritalStatus&gt;Single&lt;/MaritalStatus&gt;
      &lt;Sex&gt;Male&lt;/Sex&gt;
    &lt;/PrivateIndividual&gt;
  &lt;/PolicyholderType&gt;
  &lt;Contact&gt;Mr Bob Test&lt;/Contact&gt;
  &lt;Address&gt;
    &lt;Address1&gt;This&lt;/Address1&gt;
    &lt;Address2&gt;Test&lt;/Address2&gt;
    &lt;Address3&gt;Address&lt;/Address3&gt;
    &lt;Address4&gt;Is Fake&lt;/Address4&gt;
    &lt;Postcode&gt;BB2 1AB&lt;/Postcode&gt;
  &lt;/Address&gt;
  &lt;Phone&gt;07777777777&lt;/Phone&gt;
  &lt;Email&gt;mytestaccount@gmail.com&lt;/Email&gt;
&lt;/Policyholder&gt;
&lt;Cover&gt;
  &lt;CoverType&gt;Comprehensive&lt;/CoverType&gt;
  &lt;PolicyExcess&gt;600&lt;/PolicyExcess&gt;
  &lt;NoClaimsBonusYears&gt;9&lt;/NoClaimsBonusYears&gt;
  &lt;NCBOtherVehicle&gt;0&lt;/NCBOtherVehicle&gt;
  &lt;Use&gt;Class1&lt;/Use&gt;
&lt;/Cover&gt;
</code></pre>

<p></p>

<p>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.</p>

<p>This would be my <strong>EXPECTED</strong> output when joining TableB.</p>

<pre><code>&lt;Cover&gt;
    &lt;CoverType&gt;Comprehensive&lt;/CoverType&gt;
    &lt;PolicyExcess&gt;600&lt;/PolicyExcess&gt;
    &lt;NoClaimsBonusYears&gt;9&lt;/NoClaimsBonusYears&gt;
    &lt;NCBOtherVehicle&gt;0&lt;/NCBOtherVehicle&gt;
    &lt;Use&gt;Class1&lt;/Use&gt;
&lt;/Cover&gt;
&lt;Drivers&gt;
    &lt;Driver&gt;
        &lt;RelationshipToPolicyholder&gt;Policyholder&lt;/RelationshipToPolicyholder&gt;
        &lt;Name&gt;
            &lt;Title&gt;Mr&lt;/Title&gt;
            &lt;FirstNames&gt;Bob&lt;/FirstNames&gt;
            &lt;LastName&gt;Test&lt;/LastName&gt;
        &lt;/Name&gt;
        &lt;DateOfBirth&gt;1972-07-14&lt;/DateOfBirth&gt;
        &lt;LicenceType&gt;FullUK&lt;/LicenceType&gt;
        &lt;PeriodLicenceHeld&gt;MoreThanFiveYears&lt;/PeriodLicenceHeld&gt;
        &lt;HomeAddress&gt;
            &lt;Address1&gt;Another Test&lt;/Address1&gt;
            &lt;Address2&gt;Address&lt;/Address2&gt;
            &lt;Address3&gt;Thats Fake&lt;/Address3&gt;
            &lt;Postcode&gt;AA1 1ZZ&lt;/Postcode&gt;
        &lt;/HomeAddress&gt;
        &lt;PeriodResident&gt;MoreThanFiveYears&lt;/PeriodResident&gt;
        &lt;VehicleUseFrequency&gt;Daily&lt;/VehicleUseFrequency&gt;
        &lt;HasUseOfOtherVehicle&gt;false&lt;/HasUseOfOtherVehicle&gt;
        &lt;MainOccupation&gt;Software Engineer&lt;/MainOccupation&gt;
        &lt;HasCriminalConvictions&gt;1&lt;/HasCriminalConvictions&gt;
    &lt;/Driver&gt;
    &lt;Driver&gt;
        &lt;RelationshipToPolicyholder&gt;Spouse&lt;/RelationshipToPolicyholder&gt;
        &lt;Name&gt;
            &lt;Title&gt;Mrs&lt;/Title&gt;
            &lt;FirstNames&gt;Jill&lt;/FirstNames&gt;
            &lt;LastName&gt;Test&lt;/LastName&gt;
        &lt;/Name&gt;
        &lt;DateOfBirth&gt;1972-07-14&lt;/DateOfBirth&gt;
        &lt;LicenceType&gt;FullUK&lt;/LicenceType&gt;
        &lt;PeriodLicenceHeld&gt;MoreThanFiveYears&lt;/PeriodLicenceHeld&gt;
        &lt;HomeAddress&gt;
            &lt;Address1&gt;Another&lt;/Address1&gt;
            &lt;Address2&gt;Test&lt;/Address2&gt;
            &lt;Address3&gt;Address&lt;/Address3&gt;
            &lt;Postcode&gt;BB1 1BZ&lt;/Postcode&gt;
        &lt;/HomeAddress&gt;
        &lt;PeriodResident&gt;MoreThanFiveYears&lt;/PeriodResident&gt;
        &lt;VehicleUseFrequency&gt;Daily&lt;/VehicleUseFrequency&gt;
        &lt;HasUseOfOtherVehicle&gt;false&lt;/HasUseOfOtherVehicle&gt;
        &lt;MainOccupation&gt;Housewife&lt;/MainOccupation&gt;
        &lt;HasCriminalConvictions&gt;1&lt;/HasCriminalConvictions&gt;
    &lt;/Driver&gt;
&lt;/Drivers&gt;
</code></pre>

<p>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.</p>

<p>Anymore information needed please fire away.</p>

<p><strong>UPDATE</strong></p>

<p>My present SELECT query as requested.</p>

<pre><code>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] &gt;= dbo.Today()-7
FOR     XML PATH('Policy')
</code></pre>

<p><strong>UPDATE 2</strong></p>

<p>OK I have progress I entered in a sub-query for my 'Drivers' Select like below:</p>

<pre><code>   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" 
</code></pre>

<p>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. </p>

<p>See below:</p>

<pre><code>    &lt;Cover&gt;
  &lt;CoverType&gt;Comprehensive&lt;/CoverType&gt;
  &lt;PolicyExcess&gt;600&lt;/PolicyExcess&gt;
  &lt;NoClaimsBonusYears&gt;4&lt;/NoClaimsBonusYears&gt;
  &lt;NCBOtherVehicle&gt;0&lt;/NCBOtherVehicle&gt;
  &lt;Use&gt;Class1&lt;/Use&gt;
&lt;/Cover&gt;
&lt;Drivers&gt;
  &lt;Driver&gt;
    &lt;ID&gt;1&lt;/ID&gt;
    &lt;RelationshipToPH&gt;Policyholder&lt;/RelationshipToPH&gt;
    &lt;FirstName&gt;John&lt;/FirstName&gt;
    &lt;Surname&gt;Doe&lt;/Surname&gt;
  &lt;/Driver&gt;
  &lt;Driver&gt;
    &lt;ID&gt;2&lt;/ID&gt;
    &lt;RelationshipToPH&gt;Commonlaw Spouse&lt;/RelationshipToPH&gt;
    &lt;FirstName&gt;Bob&lt;/FirstName&gt;
    &lt;Surname&gt;Test&lt;/Surname&gt;
  &lt;/Driver&gt;
  &lt;Driver&gt;
    &lt;ID&gt;2&lt;/ID&gt;
    &lt;RelationshipToPH&gt;Policyholder&lt;/RelationshipToPH&gt;
    &lt;FirstName&gt;Jill&lt;/FirstName&gt;
    &lt;Surname&gt;Test&lt;/Surname&gt;
  &lt;/Driver&gt;
  &lt;Driver&gt;
    &lt;ID&gt;3&lt;/ID&gt;
    &lt;RelationshipToPH&gt;Policyholder&lt;/RelationshipToPH&gt;
    &lt;FirstName&gt;Jane&lt;/FirstName&gt;
    &lt;Surname&gt;Gowe&lt;/Surname&gt;
  &lt;/Driver&gt;
  &lt;Driver&gt;
    &lt;ID&gt;4&lt;/ID&gt;
    &lt;RelationshipToPH&gt;Policyholder&lt;/RelationshipToPH&gt;
    &lt;FirstName&gt;Dave&lt;/FirstName&gt;
    &lt;Surname&gt;Grace&lt;/Surname&gt;
  &lt;/Driver&gt;
</code></pre>

<p>My first row should only return the 2 Drivers with ID "2" for example.</p>