Du musst die Namespaces und Prefixes selber basteln:
USE AdventureWorks2012;
SELECT 1 as Tag,
NULL as Parent,
'urn:namespace' as [Employee!1!xmlns:ns],
E.BusinessEntityID as [Employee!1!EmpID],
NULL as [ns:Name!2!ns:FName!ELEMENT],
NULL as [ns:Name!2!ns:LName!ELEMENT]
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
null,
E.BusinessEntityID,
FirstName,
LastName
FROM HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON E.BusinessEntityID = P.BusinessEntityID
ORDER BY [Employee!1!EmpID],[ns:Name!2!ns:FName!ELEMENT]
FOR XML EXPLICIT;
