I forgot how to remove a column from being output in a
FOR XML PATH query using a group by expression. I used it before but somehow I lost the article. In the below example. I do not wish to have
idForSomething output in my result by I want to use it as condition for my inner query.
SELECT idForSomething, SUM(allSomething) AS [@sum], (SELECT innerSomething AS [@inner], innerSomething2 AS [@inner2] FROM someTable s2 WHERE s2.innerSomething = s1.idForSomething FOR XML PATH('innerlist'), TYPE) FROM someTable s1 WHERE idForSomething = 1 GROUP BY idForSomething FOR XML PATH('listofsomethings')
Added XML Body:
<listofsomethings @sum="10"> <innerlist @inner="..." @inner2="..." /> <innerlist @inner="..." @inner2="..." /> <innerlist @inner="..." @inner2="..." /> </listofsomethings>
I will look around again online, but I asking for the syntax to SQL Server to NOT USE "idForSomething" column in the final output. I thought it was something like NOOUTPUT but I can't remember and it does not work.