sql - XMLELEMENT 属性组

Question

我们能够生成如下所示的 XML 结构，其中数据是线性的，没有关于模型的分组。

<transaction>
    <header>
        <trantype>siv</trantype>
        <options>ModelMasterEdit</options>
    </header>
    <detail>
        <record>
            <Description>test_060420</Description>
            <Details>Test Details</Details>
            <Canonical>A1863</Canonical>
            <Classification>PH</Classification>
            <Tree>XM</Tree>
            <Manufacturer>Apple</Manufacturer>
            <Forecast>Apple Smart Phones</Forecast>
            <Category>Cellular Phones</Category>
            <SerialCaption>Serial Number</SerialCaption>
            <Description>test_060421</Description>
            <Details>Test Details</Details>
            <Canonical>A1863</Canonical>
            <Classification>PH</Classification>
            <Tree>XM</Tree>
            <Manufacturer>Apple</Manufacturer>
            <Forecast>Apple Smart Phones</Forecast>
            <Category>Cellular Phones</Category>
            <SerialCaption>Serial Number</SerialCaption>
        </record>
    </detail>
</transaction>

但是，我们需要通过根据型号对模型属性进行分组来以以下格式输出

<transaction>
    <header>
        <trantype>siv</trantype>
        <options>ModelMasterEdit</options>
    </header>
    <detail>
        <record>
            <Description>test_060420</Description>
            <Details>Test Details</Details>
            <Canonical>A1863</Canonical>
            <Classification>PH</Classification>
            <Tree>XM</Tree>
            <Manufacturer>Apple</Manufacturer>
            <Forecast>Apple Smart Phones</Forecast>
            <Category>Cellular Phones</Category>
            <SerialCaption>Serial Number</SerialCaption>
        </record>
        <record>
            <Description>test_060421</Description>
            <Details>Test Details</Details>
            <Canonical>A1863</Canonical>
            <Classification>PH</Classification>
            <Tree>XM</Tree>
            <Manufacturer>Apple</Manufacturer>
            <Forecast>Apple Smart Phones</Forecast>
            <Category>Cellular Phones</Category>
            <SerialCaption>Serial Number</SerialCaption>
        </record>
    </detail>
</transaction>

我们在下面创建 SQL 查询以生成第一个 XML，但无法生成所需的第二个 xml

SELECT XMLELEMENT ("transaction",XMLELEMENT("header", XMLELEMENT ("trantype", 'siv'), XMLELEMENT ("options", 'ModelMasterEdit')),
        XMLELEMENT ( "detail",
        XMLELEMENT (
           "record",
           XMLAGG (
               XMLELEMENT (evalname(attribute_name), attribute_value)))))
              FROM XXCMST_INV_CMT_PRODUCT_TBL i
                 WHERE     soa_instance_id = 64202001
                 AND source_system = 'EIS'

表数据：

标签： sqlgroupingxml-attributexmlelement