As I understand it, there are 4 different options when using FOR XML in a SQL query: RAW, AUTO, EXPLICIT, and PATH. I think for my example below, I want to largely use PATH, but I end up getting erroneous values that come through.

Suppose I've got a query that returns three columns using the basic query below:

SELECT p.PARTY_ID
,hc.PARTY_HAT_COLOR
,pt.PARTY_TYPE_NAME
FROM PARTY p
LEFT JOIN HAT_COLOR hc ON p.HAT_COLOR_ID = hc.HAT_COLOR_ID
LEFT JOIN PARTY_TYPE pt ON p.PART_TYPE_ID = pt.PARTY_TYPE_ID

Let's assume that it returns just two rows with party ID 101 and 102. The rest of the column data can be found in the example output below in the PARTY_INFO section wrapper.

Here's the formatting I'm looking to achieve in the output, where the

    PARTY_TOWN_USA_LLC
    WE_HAVE_FUN_HERE
    123_MAIN_ST_ANYTOWN_USA
        
            STATIC_TEXT_ROW_5_HERE
            STATIC_TEXT_ROW_6_HERE
            STATIC_TEXT_ROW_7_HERE
                
                    
                        101
                        Blue
                        Retirement
                    
                    
                        102
                        Red
                        Birthday
                                        
                
          

How can I build this where there's multiple siblings and children despite them being static values for the header rows?