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