代码之家 › 专栏 › 技术社区 › Matt W

用FOR XML、PATH嵌套XML元素

sqlxml tsql sql-server

Matt W · 技术社区 · 6 年前

我有桌上活动:

Name    Event       Gift        Label
Jeff    Birthday    Card        Happy Birthday
Jeff    Birthday    Present     Jeff's prezzy
Maisey  Promotion   Cake        Well Done!
Maisey  Birthday    Card        Happy Birthday
Maisey  Birthday    Cake        Another Year Older!

下面是生成该表的代码:

DECLARE @TempTable TABLE
(
    [name] VARCHAR(250),
    [event] VARCHAR(250),
    [gift] VARCHAR(250),
    [label] VARCHAR(2500)
);

INSERT INTO @TempTable([name], [event], [gift], [label]) VALUES
('Jeff','Birthday','Card','Happy Birthday'),
('Jeff','Birthday','Present','Jeff`s prezzy'),
('Maisey','Promotion','Cake','Well Done!'),
('Maisey','Birthday','Card','Happy Birthday'),
('Maisey','Birthday','Cake','Another Year Older!')

我想从中生成以下XML:

<celebrations>
    <person name="Jeff">
        <events>
            <event name="Birthday">
                <gifts>
                    <gift name="Card" Value="Happy Birthday" />
                    <gift name="Present" Value="Jeff's prezzy" />
                </gifts>
            </event>
        </events>
    </person>
    <person name="Maisey">
        <events>
            <event name="Promotion">
                <gifts>
                    <gift name="Cake" value="Well Done!" />
                </gifts>
            </event>
            <event name="Birthday">
                <gifts>
                    <gift name="Card" value="Happy Birthday" />
                    <gift name="Cake" value="Another Year Older!" />
                </gifts>
            </event>
        </events>
    </person>
</celebrations>

我当前的SQLXML如下所示:

SELECT (
        SELECT (
                SELECT T1.*
                FOR XML path('')
                    ,root('gifts')
                    ,type
                )
        FROM @TempTable AS T1
        FOR XML path('person')
            ,type
        )
FOR XML path('persons')

目前正在生产:

<persons>
  <person>
    <gifts>
      <name>Jeff</name>
      <event>Birthday</event>
      <gift>Card</gift>
      <label>Happy Birthday</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Jeff</name>
      <event>Birthday</event>
      <gift>Present</gift>
      <label>Jeff`s prezzy</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Promotion</event>
      <gift>Cake</gift>
      <label>Well Done!</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Birthday</event>
      <gift>Card</gift>
      <label>Happy Birthday</label>
    </gifts>
  </person>
  <person>
    <gifts>
      <name>Maisey</name>
      <event>Birthday</event>
      <gift>Cake</gift>
      <label>Another Year Older!</label>
    </gifts>
  </person>
</persons>

1 回复 | 直到 6 年前

Matt W 6 年前

试试这个,魔法是具有 GROUP BY

DECLARE @TempTable TABLE
(
    [name] VARCHAR(250),
    [event] VARCHAR(250),
    [gift] VARCHAR(250),
    [label] VARCHAR(2500)
);

INSERT INTO @TempTable([name], [event], [gift], [label]) VALUES
('Jeff','Birthday','Card','Happy Birthday'),
('Jeff','Birthday','Present','Jeff`s prezzy'),
('Maisey','Promotion','Cake','Well Done!'),
('Maisey','Birthday','Card','Happy Birthday'),
('Maisey','Birthday','Cake','Another Year Older!')

SELECT t1.[Name] AS [@name]
      ,(
        SELECT T2.[Event] AS [@name]
              ,(
                SELECT T3.gift AS [@name], T3.label AS [@value]
                FROM @TempTable T3
                WHERE T1.[Name]=T3.[Name] AND T2.[event] = T3.[event]
                GROUP BY T3.gift, T3.label
                FOR XML PATH('gift'),ROOT('gifts'),TYPE
               )
        FROM @TempTable T2
        WHERE T1.[Name]=T2.[Name]
        GROUP BY T2.[event]
        FOR XML PATH('event'),ROOT('events'),TYPE
       ) 
FROM @TempTable AS T1
GROUP BY t1.[Name]
FOR XML PATH('person'),ROOT('celebrations'),TYPE;

结果

<celebrations>
  <person name="Jeff">
    <events>
      <event name="Birthday">
        <gifts>
          <gift name="Card" value="Happy Birthday" />
          <gift name="Present" value="Jeff`s prezzy" />
        </gifts>
      </event>
    </events>
  </person>
  <person name="Maisey">
    <events>
      <event name="Birthday">
        <gifts>
          <gift name="Cake" value="Another Year Older!" />
          <gift name="Card" value="Happy Birthday" />
        </gifts>
      </event>
      <event name="Promotion">
        <gifts>
          <gift name="Cake" value="Well Done!" />
        </gifts>
      </event>
    </events>
  </person>
</celebrations>