Help me about syntax STUFF (SELECT... for XML PATH)
Based off of your examples queries, this should work out for you (Comments Included):

-- works
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT 
                            ', ' + p.pname 
                FROM        dbo.products AS p 
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid --Join the products to the Order Details...
                WHERE od1.oid = o1.oid  -- Only for the "main" orders
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name], -- remove the first comma and space.
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid
GROUP BY    o1.oname, o1.oid;


However, your expected output doesn't have any Product C so maybe you don't want to show them. In which case, you would need to join back and restrict it away:

-- without Product C?
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT 
                            ', ' + p.pname 
                FROM        dbo.products AS p 
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid 
                WHERE od1.oid = o1.oid
                AND p.pid &lt;&gt; 'p3'
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name],
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid

-- Add this join to remove Product C.
INNER JOIN  dbo.products AS [p1] ON p1.pid =o2.pid
                AND p1.pid &lt;&gt; 'p3'

GROUP BY    o1.oname, o1.oid;


But then you're expected output doesn't have Order 3, so you'd need to restrict that away as well. However I'm not going to do that as you should have the info you need to do that yourself.

Best of luck!