After hours of desperation and hundreds of trials & errors, I’ve come up with the solution below.

I had the same issue, when I wanted just one xmlns attribute, on the root node only. But I also had a very difficult query with lot’s of subqueries and FOR XML EXPLICIT method alone was just too cumbersome. So yes, I wanted the convenience of FOR XML PATH in the subqueries and also to set my own xmlns.

I kindly borrowed the code of 8kb’s answer, because it was so nice. I tweaked it a bit for better understanding. Here is the code:

DECLARE @Order TABLE (OrderID INT, OrderDate DATETIME)    
DECLARE @OrderDetail TABLE (OrderID INT, ItemID VARCHAR(1), Name VARCHAR(50), Qty INT)    
INSERT @Order VALUES (1, '2010-01-01'), (2, '2010-01-02')    
INSERT @OrderDetail VALUES (1, 'A', 'Drink',  5),
                           (1, 'B', 'Cup',    2),
                           (2, 'A', 'Drink',  2),
                           (2, 'C', 'Straw',  1),
                           (2, 'D', 'Napkin', 1)

-- Your ordinary FOR XML PATH query
DECLARE @xml XML = (SELECT OrderID AS "@OrderID",
                        (SELECT ItemID AS "@ItemID", 
                                Name AS "data()" 
                         FROM @OrderDetail 
                         WHERE OrderID = o.OrderID 
                         FOR XML PATH ('Item'), TYPE)
                    FROM @Order o 
                    FOR XML PATH ('Order'), ROOT('dummyTag'), TYPE)

-- Magic happens here!       
SELECT 1 AS Tag
      ,NULL AS Parent
      ,@xml AS [xml!1!!xmltext]
      ,'http://test.com/order' AS [xml!1!xmlns]
FOR XML EXPLICIT

Result:

<xml xmlns="http://test.com/order">
  <Order OrderID="1">
    <Item ItemID="A">Drink</Item>
    <Item ItemID="B">Cup</Item>
  </Order>
  <Order OrderID="2">
    <Item ItemID="A">Drink</Item>
    <Item ItemID="C">Straw</Item>
    <Item ItemID="D">Napkin</Item>
  </Order>
</xml>

If you selected @xml alone, you would see that it contains root node dummyTag. We don’t need it, so we remove it by using directive xmltext in FOR XML EXPLICIT query:

,@xml AS [xml!1!!xmltext]

Although the explanation in MSDN sounds more sophisticated, but practically it tells the parser to select the contents of XML root node.

Not sure how fast the query is, yet currently I am relaxing and drinking Scotch like a gent while peacefully looking at the code…