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.
Attribute-centric column '@sum' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH
. If you remove the columnidForSomething
you have a working query. Question is if it produces the XML you want. – Mikael Eriksson Jan 23 at 17:35