Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
What happens if you just remove the column from the field list? – Mikael Eriksson Jan 23 at 17:27
2  
What exactly is the XML output you want? Where do you want the SUM to show up? Can you show what the output should look like in the question? – Aaron Bertrand Jan 23 at 17:33
4  
The query you have here 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 column idForSomething you have a working query. Question is if it produces the XML you want. – Mikael Eriksson Jan 23 at 17:35

1 Answer

I found out that I am unable to hide a column using FOR XML PATH, which is what I was trying to do. I have to use FOR XML EXPLICIT in order to achieve this result.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.