1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
DECLARE @EPC_PROMO TABLE
(
i INT,
Id VARCHAR(20),
THEME VARCHAR(20),
LANG_ID VARCHAR(20),
FROM_DATE DATETIME,
TO_DATE DATETIME,
VALUE VARCHAR(20)
);
INSERT INTO @EPC_PROMO VALUES (1,'Home', 'Default', 'eng', '1900-01-01', '2000-01-01', 'default');
INSERT INTO @EPC_PROMO VALUES (2,'Home', 'Default', 'eng', '2000-01-01', '2100-01-01', 'default');
SELECT
1 AS Tag,
NULL AS Parent,
Id AS [PromoType!1!Id],
THEME AS [PromoType!1!Theme],
NULL AS [PromoName!2!!ELEMENT],
NULL AS [PromoName!2!LANGUAGE],
NULL AS [PromoName!2!FromDate],
NULL AS [PromoName!2!ToDate]
FROM @EPC_PROMO
UNION
SELECT
2 AS Tag,
1 AS Parent,
Id,
THEME,
VALUE,
LANG_ID,
CONVERT(VARCHAR, FROM_DATE, 112),
CONVERT(VARCHAR, TO_DATE, 112)
FROM @EPC_PROMO
FOR XML EXPLICIT, ROOT('Promos') |