对于XML路径-动态节点？

Question

我有一个示例表。

rate_id|account_id|definition|category|pmpm_override|amount

1|a1|PSPM|c1|null|100

2|a1|PSPM|c2|空|200

3|a1|PC|c3|空|150

4|a1|PC|c4|空|250

5|a2|PMPM|c5|TRUE|400

6|a2|PMPM|c3|TRUE|500

7|a3|PMPM|c2|FALSE|600

问题集中在'definition‘列上，因为它的值决定了元素category、amount和pmpm_override所在的节点名称。

例如，当definition=PSPM节点是pspmrate _ pcrate时，当definition=PC节点是pcrate时，等等。

使用FOR XML路径，我希望返回以下内容：

<?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 XML PATH(‘pspmrate’)对其进行了硬编码)，因此rate ID3-7具有错误的节点。

在现实中，查询代码要长得多，结构也要复杂得多，但这个示例代表了我正在寻找的东西--我想有办法使用动态sql来实现这一点，但如果可能的话，我会尽量避免处理和排除大型查询字符串的故障。

我想我正在寻找一种动态确定XML路径的方法。

如有任何建议，欢迎光临。

谢谢。

Answer 1

我认为如果您有一个已知数量的定义，您可以使用case表达式来完成此操作：

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');

如果不是，那么您将需要动态sql来构建case表达式：

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;