如何获取 XML 子查询以将属性附加到父级？

Question

给定下面的查询，是否可以将“Metadata”元素下找到的元素作为“Event”元素上的属性，而不更改子查询的 where 子句（即 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

试试这个：

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');