Início / dba / Perguntas / 329206
Accepted
Himanshuman
Asked: 2023-07-12 17:52:34 +0800 CST

Gere XML para cada grupo na tabela

  • 772

Eu tenho os dados abaixo com os quais preciso agrupar buideduidaid

INSERT INTO XmlTable (id, cid, aid, buid, duid) 
VALUES(624220, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(625475, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(659989, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(660076, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(660089, 68487905, 33, '7C06311E', '7C06311E')
,(660361, 68487905, 33, '7C06311E', '7C06311E')
,(662592, 68487905, 33, '3501549D', 'AD8AB9EC')
,(663011, 68487905, 33, '3501549D', 'AD8AB9EC');

e gere a lista xml correspondente que contém os respectivos id, cidpara cada um desses grupos.

<IList>
    <drive aid="33" buid="77D8B242" duid="DF6DC7D0">
        <files id="624220" cid="68487905"/>
        <files id="625475" cid="68487905"/>
        <files id="659989" cid="68487905"/>
        <files id="660076" cid="68487905"/>
    </drive>
    <drive aid="33" buid="7C06311E" duid="7C06311E">
        <files id="660089" cid="68487905"/>
        <files id="660361" cid="68487905"/>
    </drive>
    <drive aid="33" buid="3501549D" duid="AD8AB9EC">
        <files id="662592" cid="68487905"/>
        <files id="663011" cid="68487905"/>
    </drive>
</IList>
sql-server

1 respostas

  1. Best Answer
    DECLARE @XmlTable TABLE
(
    id INT,
    cid INT,
    aid INT,
    buid VARCHAR(10),
    duid VARCHAR(10)
);

INSERT INTO @XmlTable (id, cid, aid, buid, duid) 
VALUES(624220, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(625475, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(659989, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(660076, 68487905, 33, '77D8B242', 'DF6DC7D0')
,(660089, 68487905, 33, '7C06311E', '7C06311E')
,(660361, 68487905, 33, '7C06311E', '7C06311E')
,(662592, 68487905, 33, '3501549D', 'AD8AB9EC')
,(663011, 68487905, 33, '3501549D', 'AD8AB9EC');

SELECT
    aid AS '@aid',
    buid AS '@buid',
    duid AS '@duid',
    (
        SELECT
            id AS '@id',
            cid AS '@cid'
        FROM
            @XmlTable AS T2
        WHERE
            T2.aid = T1.aid
            AND T2.buid = T1.buid
            AND T2.duid = T1.duid
        FOR XML PATH('files'), TYPE
    )
FROM
    @XmlTable AS T1
GROUP BY
    aid, buid, duid
FOR XML PATH('drive'), ROOT('IList');

    Para mim, isso tem a saída certa:

    insira a descrição da imagem aqui

    • 3

relate perguntas