SQL Server 2005 - Nested XML

Question

I used this question/answer as a starting point. SQL Server : nesting elements with FOR XML PATH

I'm trying to get to this output.

<Account ExID="Customer Numer 1">
<AccountData>
    <TwoColumnsChart>
            <CategoryName ID="Product Line 1">
                <ChartName>Prior vs current period sales for Product Line 1 </ChartName>
            <ValuesDescriptions>
                <Value1>Target</Value1>
                <Value2>Actual</Value2>
            </ValuesDescriptions>
            <SalesByFields>
                <Field><FieldName>Item</FieldName><FieldValue1>3100 (current Period)</FieldValue1><FieldValue2>2324 (prior Period)</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>3100</FieldValue1><FieldValue2>2324</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
            </SalesByFields>
                <TotalSales>
                    <Field><FieldName>Total prior period</FieldName><FieldValue>$32,211</FieldValue></Field>
                    <Field><FieldName>Total current period</FieldName><FieldValue>$24,215</FieldValue></Field>
                </TotalSales>
            </CategoryName>
            <CategoryName ID="Product Line 2">
                <ChartName>Prior vs current period sales for Product Line 2</ChartName>
            <ValuesDescriptions>
                <Value1>Target</Value1>
                <Value2>Actual</Value2>
            </ValuesDescriptions>
            <SalesByFields>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>3100</FieldValue1><FieldValue2>2324</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
            </SalesByFields>
                <TotalSales>
                    <Field><FieldName>Total prior period</FieldName><FieldValue>$32,211</FieldValue></Field>
                    <Field><FieldName>Total current period</FieldName><FieldValue>$24,215</FieldValue></Field>
                </TotalSales>
            </CategoryName>
            <CategoryName ID="Product Line 3">
                <ChartName>Prior vs current period sales for Product Line 3</ChartName>
            <ValuesDescriptions>
                <Value1>Target</Value1>
                <Value2>Actual</Value2>
            </ValuesDescriptions>
            <SalesByFields>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>3100</FieldValue1><FieldValue2>2324</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>1244</FieldValue1><FieldValue2>1000</FieldValue2></Field>
                <Field><FieldName>Item</FieldName><FieldValue1>2300</FieldValue1><FieldValue2>3000</FieldValue2></Field>
            </SalesByFields>
                <TotalSales>
                    <Field><FieldName>Total prior period</FieldName><FieldValue>$32,211</FieldValue></Field>
                    <Field><FieldName>Total current period</FieldName><FieldValue>$24,215</FieldValue></Field>
                </TotalSales>
            </CategoryName>     
        </TwoColumnsChart>
</AccountData>

My #TempTable will be a custom table I build from the data warehouse, so I'm using some sample data first. Is there an easier way to get to the XML then what I'm doing?

CREATE TABLE #TempTable 
(
     [FieldName] char(15),
     [FieldValue1] char(15),
     [FieldValue2] char(15),
     [CategoryName] char(15), 
     [Customer] char(15)
)

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test1Cust1', '100', '150', 'ProductLine1', 'Customer1')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test1Cust1', '100', '15110', 'ProductLine1', 'Customer1')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test1Cust2', '100', '150', 'ProductLine1', 'Customer2')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test1Cust2', '110', '15110', 'ProductLine1', 'Customer2')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Tes2Cust1', '200', '250', 'ProductLine2', 'Customer1')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Tes2Cust2', '200', '250', 'ProductLine2', 'Customer2')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test3Cust1', '300', '350', 'ProductLine3', 'Customer1')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test3Cust2', '300', '350', 'ProductLine3', 'Customer2')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test4Cust1', '400', '450', 'ProductLine4', 'Customer1')

INSERT INTO #TempTable([FieldName], [FieldValue1], [FieldValue2], [CategoryName], [Customer]) 
VALUES ('Test4Cust2', '400', '450', 'ProductLine4', 'Customer2')

SELECT 
    (SELECT 
         RTRIM(T1.Customer) AS "@ExID",
         (SELECT 
              RTRIM(T2.CategoryName) AS "@ID",
              (SELECT 
                   RTRIM(T3.FieldName) AS FieldName,
                   RTRIM(T3.FieldValue1) AS FieldValue1,
                   RTRIM(T3.FieldValue2) AS FieldValue2
               FROM 
                   #TempTable T3
               WHERE 
                   T2.categoryname = T3.CategoryName 
                   AND T2.Customer = T3.Customer
               FOR XML path('Field'), root('SalesByFields'), type)
          FROM 
              #TempTable AS T2
          WHERE 
              T1.Customer = T2.Customer
          GROUP BY 
              T2.CategoryName, T2.Customer
          FOR XML path('CategoryName'), type)
    FROM 
        #TempTable AS T1
    GROUP BY 
        T1.Customer
    FOR XML path('Account'), TYPE)
FOR XML path('Accounts')

DROP TABLE #TempTable

Answer 1

This is what I came up with which built the XML the way I needed it. For anyone interested, this is for loading dashboard reports on Pepperi mobile software.

SELECT (
    SELECT RTRIM(T1.Customer) AS "@ExID",
            (
                SELECT RTRIM(T2.CategoryName) AS "@ID"
                        , 'Prior vs current period sales for ' + RTRIM(T2.CategoryName) AS "ChartName"
                        , 'Target' AS "ValuesDescription/Value1"
                        , 'Actual' AS "ValuesDescription/Value2"
                        ,
                        (
                            SELECT RTRIM(T3.FieldName) AS FieldName
                                    , SUM(T3.FieldValue1) AS FieldValue1
                                    , SUM(T3.FieldValue2) AS FieldValue2
                            FROM #TempTable T3
                            WHERE T2.categoryname = T3.CategoryName AND
                                    T2.Customer = T3.Customer
                            GROUP BY T3.FieldName
                            FOR XML PATH('Field')
                                ,ROOT('SalesByFields')
                                ,TYPE
                        )
                        ,
                        (
                            SELECT T4.FieldName AS FieldName
                                    , SUM(T4.FieldValue) AS FieldValue
                            FROM #CurVsPrior T4
                            WHERE T2.Customer = T4.AddressID AND
                                    T2.CategoryName = T4.Brand
                            GROUP BY  T4.FieldName
                            FOR XML PATH('Field')
                                ,ROOT('TotalSales')
                                ,TYPE
                        )
                FROM #TempTable AS T2
                WHERE T1.Customer = T2.Customer
                GROUP BY T2.CategoryName, T2.Customer
                FOR XML PATH('CategoryName')
                    ,TYPE
            ) AS "AccountData/TwoColumnsChart"
    FROM #TempTable AS T1
    GROUP BY T1.Customer
    FOR XML PATH('Account')
        ,TYPE
    )

FOR XML PATH('Accounts')