How can I get an XML subquery to append attributes to parent?

Question

Given the query below, is it doable to have elements found under the "Metadata" element as attributes on the "Event" element, without changing the where clause of the subquery (I.e. WHERE UniqueID = t1.UniqueID AND ID = MAX(t1.ID))?

DECLARE @Event      TABLE
(
    UniqueID    VARCHAR(3),
    ID          INT,
    Name        VARCHAR(25),
    Latitude    FLOAT,
    Longitude   FLOAT,
    PRIMARY KEY(UniqueID, ID)
);
DECLARE @Vehicle1       TABLE
(
    UniqueID    VARCHAR(3),
    ID          INT,
    Column1 VARCHAR(25)
);
DECLARE @Vehicle2       TABLE
(
    UniqueID    VARCHAR(3),
    ID          INT,
    Column1 VARCHAR(25)
);

INSERT INTO @Event VALUES ('ABC', 1, 'LPR', 1.234, 2.345)
INSERT INTO @Event VALUES ('ABC', 2, 'LPR', 2.234, 3.345)
INSERT INTO @Event VALUES ('ABC', 3, 'LPR', 3.234, 4.345)
INSERT INTO @Event VALUES ('ABC', 4, 'LPR', 4.234, 5.345)

INSERT INTO @Event VALUES ('DEF', 1, 'LPR', 1.234, 2.345)

INSERT INTO @Event VALUES ('GHI', 1, 'Manual Scan', 1.234, 2.345)
INSERT INTO @Event VALUES ('GHI', 2, 'Manual Scan', 2.234, 3.345)

INSERT INTO @Vehicle1 VALUES ('ABC', 1, 'Plate # 1')
INSERT INTO @Vehicle1 VALUES ('ABC', 1, 'Plate # 2')

INSERT INTO @Vehicle2 VALUES ('GHI', 1, 'Plate # 1')
INSERT INTO @Vehicle2 VALUES ('GHI', 2, 'Plate # 2')
INSERT INTO @Vehicle2 VALUES ('GHI', 3, 'Plate # 3')

SELECT
    UniqueID AS UniqueID,

    (SELECT
        ID,
        Name,
        Latitude,
        Longitude
    FROM @Event 
    WHERE UniqueID = t1.UniqueID AND ID = MAX(t1.ID)
    FOR XML RAW ('Metadata'), ELEMENTS, TYPE),

    (SELECT
        Column1
    FROM @Vehicle1
    WHERE UniqueID = t1.UniqueID
    FOR XML RAW ('Row'), TYPE, ROOT ('Vehicle1')),

    (SELECT
        Column1
    FROM @Vehicle2
    WHERE UniqueID = t1.UniqueID
    FOR XML RAW ('Row'), TYPE, ROOT ('Vehicle2'))

FROM @Event t1
GROUP BY t1.UniqueID
FOR XML RAW ('Event'), TYPE, ROOT ('Events')

Answer 1

Try this:

SELECT
    UniqueID        AS [@UniqueID],

    x.ID            AS [@ID],
    x.Name          AS [@Name],
    x.Latitude      AS [@Latitude],
    x.Longitude     AS [@Longitude],

    (SELECT
        Column1
    FROM @Vehicle1
    WHERE UniqueID = t1.UniqueID
    FOR XML RAW ('Row'), TYPE, ROOT ('Vehicle1')),

    (SELECT
        Column1
    FROM @Vehicle2
    WHERE UniqueID = t1.UniqueID
    FOR XML RAW ('Row'), TYPE, ROOT ('Vehicle2'))

FROM
(
    SELECT
        UniqueID,
        MAX(t1.ID) AS MaxID
    FROM
        @Event AS t1
    GROUP BY
        t1.UniqueID
) AS t1
CROSS APPLY
    (
        SELECT
            ID,
            Name,
            Latitude,
            Longitude
        FROM
            @Event 
        WHERE
            UniqueID = t1.UniqueID AND
            ID = t1.MaxID
    ) AS x
ORDER BY
    T1.UniqueID    
FOR XML PATH('Event'), TYPE, ROOT ('Events');