FOR XML PATH - dynamic node?

Question

I have this sample table.

rate_id|account_id|definition|category|pmpm_override|amount

1|a1|PSPM|c1|null|100

2|a1|PSPM|c2|null|200

3|a1|PC|c3|null|150

4|a1|PC|c4|null|250

5|a2|PMPM|c5|TRUE|400

6|a2|PMPM|c3|TRUE|500

7|a3|PMPM|c2|FALSE|600

The problem focuses on the 'definition' column as its value determines the node name that elements category, amount, and pmpm_override are under.

e.g., When definition=PSPM the node is pspmrate, when definition=PC node is pcrate, etc.

Using the FOR XML PATH I am looking to return the following:

<?xml version="1.0" encoding="utf-8"?>
<Rates>
    <rate_id id="1">
    <account_id>a1</account_id>
    <pspmrate>
        <category>c1</category>
        <amount>100</amount>
    </pspmrate>
    </rate_id>
    <rate_id id="2">
    <account_id>a1</account_id>
    <pspmrate>
        <category>c2</category>
        <amount>200</amount>
    </pspmrate>
    </rate_id>
    <rate_id id="3">
    <account_id>a1</account_id>
    <pcrate>
        <category>c3</category>
        <amount>150</amount>        
    </pcrate>
    </rate_id>
    <rate_id id="4">
    <account_id>a1</account_id>
    <pcrate>
        <category>c4</category>
        <amount>250</amount>        
    </pcrate>
    </rate_id>
    <rate_id id="5">
    <account_id>a2</account_id>
    <pmpmrate>
        <category>c5</category>
        <amount>400</amount>        
        <pmpm_override>true</pmpm_override>
    </pmpmrate>
    </rate_id>
    <rate_id id="6">
    <account_id>a2</account_id>
    <pmpmrate>
        <category>c3</category>
        <amount>500</amount>        
        <pmpm_override>true</pmpm_override>
    </pmpmrate>
    </rate_id>
    <rate_id id="7">
    <account_id>a3</account_id>
    <pmpmrate>
        <category>c2</category>
        <amount>600</amount>        
        <pmpm_override>false</pmpm_override>
    </pmpmrate>
    </rate_id>
</Rates>

For now I have a query that returns the correct structure minus the node name (I have hard coded this with FOR XML PATH('pspmrate')) and so the rate ids 3-7 have the wrong node.

In reality the query code is much much larger and structure is a bit more complex but this sample is representative of what I'm looking for - I imagine there is way to do perform this with dynamic sql but I'm trying to avoid working and troubleshooting a huge query string if possible.

I guess I'm looking for a way to dynamically determine the XML PATH.

Any suggestions are appreciated.

Thanks.

Answer 1

I think you can do this with a case expression if you have a known number of definitions:

SELECT  [@id] = rate_id,
        account_id,
        CASE definition
            WHEN 'PC' THEN (SELECT pmpm_override, amount, category FOR XML PATH('pcrate'), TYPE)
            WHEN 'PMPM' THEN (SELECT pmpm_override, amount, category FOR XML PATH('pmpmrate'), TYPE) 
            WHEN 'PSPM' THEN (SELECT pmpm_override, amount, category FOR XML PATH('pspmrate'), TYPE)
        END
FROM    T
FOR XML PATH('rate_id'), ROOT('rates');

If not then you will need dynamic sql to build your case expression:

DECLARE @SQL NVARCHAR(MAX);
SELECT  @SQL = 'SELECT  [@id] = rate_id,
                        account_id,
                        CASE definition
                            ' + (SELECT 'WHEN ''' + Definition + 
                                        ''' THEN (SELECT pmpm_override, amount, category FOR XML PATH(''' + 
                                        LOWER(Definition) + 'rate''), TYPE) '
                                FROM    T
                                GROUP BY Definition
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') + '
                        END
                FROM    T
                FOR XML PATH(''rate_id''), ROOT(''rates'');';

EXECUTE sp_executesql @SQL;