Use:
SELECT (SELECT t.type, t.brand
FROM dbo.TVs t
FOR XML PATH('tv'), ROOT('tvs'), ELEMENTS, TYPE),
(SELECT r.type, r.brand
FROM dbo.Receivers r
FOR XML PATH('receiver'), ROOT('receivers'), ELEMENTS, TYPE)
FOR XML PATH('filterdata')
测试使用:
WITH tvs AS (
SELECT 'LCD' AS type, 'Samsung' AS brand
UNION ALL
SELECT 'LCD' AS type, 'Panasonic' AS brand),
receivers AS (
SELECT 'Surround 7.1' AS type, 'Onkyo' AS brand
UNION ALL
SELECT 'Surround 7.1', 'Denon')
SELECT (SELECT t.type, t.brand
FROM tvs t
FOR XML PATH('tv'), ROOT('tvs'), ELEMENTS, TYPE),
(SELECT r.type, r.brand
FROM receivers r
FOR XML PATH('receiver'), ROOT('receivers'), ELEMENTS, TYPE)
FOR XML PATH('filterdata')