OK, found another example.
SET NOCOUNT ON
DECLARE
@strSQL NVARCHAR(MAX)
SET @strSQL = '' -- required or the concatenation will return null
;WITH cteStore (SalesPersonID,TerritoryID) -- create some sample data
AS
(
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,2 UNION ALL
SELECT 7,2
),
cteSalesPerson (SalesPersonID,SalesPersonName,ModifiedDate)
AS
(
SELECT 1,'George Washington','2013-03-31' UNION ALL
SELECT 2,'John Adams','2013-02-28' UNION ALL
SELECT 3,'Thomas Jefferson','2013-02-15' UNION ALL
SELECT 4,'James Madison','2013-02-01' UNION ALL
SELECT 5,'James Monroe','2013-01-31' UNION ALL
SELECT 6,'John Q Adams','2013-01-22' UNION ALL
SELECT 1,'George Washington','2013-03-21' UNION ALL
SELECT 7,'Andew Jackson','2013-01-13' UNION ALL
SELECT 3,'Thomas Jefferson','2013-02-05'
)
-- create a pseudo-XML string
SELECT
@strSQL = @strSQL + CAST(r.strXML AS NVARCHAR(MAX))
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY p1.SalesPersonID ORDER BY p1.SalesPersonID) AS rn
,(SELECT
(SELECT
s.SalesPersonID
,s.TerritoryID
FROM
cteStore AS s
WHERE
s.SalesPersonID = t.N
FOR XML PATH(''), TYPE)
,(SELECT
(SELECT
p.SalesPersonName AS 'Name'
,p.ModifiedDate
FROM
cteSalesPerson AS p
WHERE
p.SalesPersonID = t.N
ORDER BY
p.SalesPersonID
FOR XML PATH('Sale'), TYPE)
FOR XML PATH('Sales'), TYPE)
FOR XML PATH(''), TYPE)
AS strXML
FROM
cteSalesPerson p1
INNER JOIN
dbo.Tally t
ON t.N = p1.SalesPersonID
) r
WHERE
rn = 1
ORDER BY
rn
-- convert the string into XML
SELECT CONVERT(XML,@strSQL) AS XML_Result