Как получить значения из XML, где имена совпадают

Пожалуйста, попробуйте следующее решение.

Он будет работать, начиная с SQL Server 2017 и далее.

Примечательные моменты:

• OUTER APPLY имитирует отношения родитель/потомок между двумя уровнями во входном XML.
• XQuery .nodes() выражения XPath метода были правильно скорректированы.
• t1(p) и t2(c) являются псевдонимами для родительских /дочерних (один ко многим) реляционных наборов результатов.
• Дополнительно TRIM(...) функция используется для удаления фигурных скобок. Эта функция была введена в SQL Server 2017.

SQL

DECLARE @XMLData XML = 
N'<Root>
    <Data>
        <Data>
            <table>
                <id>{54bd0b6f-693e-476f-a80e-0094de4cf3b4}</id>
                <rows>
                    <row>
                        <columns>
                            <column name="ec_date" value="27-July-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="Reiskosten woon-werk"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP504"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                </rows>
                <key>DefaultKey</key>
                <total>145.14</total>
                <AddOnKey>0</AddOnKey>
                <data/>
                <parameters/>
            </table>
        </Data>
    </Data>
    <Data>
        <Data>
            <table>
                <id>{9adfe4dd-49be-48fb-a838-009e861be8bd}</id>
                <rows>
                    <row>
                        <columns>
                            <column name="ec_date" value="24-August-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="reiskosten Alkmaar-ZaandamXP502"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP502"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                    <row>
                        <columns>
                            <column name="ec_date" value="26-August-2021"
                                    type="System.DateTime"/>
                            <column name="ec_description"
                                    value="reiskosten Alkmaar-Zaandam"
                                    type="System.String"/>
                            <column name="ec_Type" value="XP502"
                                    type="System.String"/>
                            <column name="ec_currency" value="EUR"
                                    type="System.String"/>
                        </columns>
                    </row>
                </rows>
                <key>DefaultKey</key>
                <total>8.82</total>
                <AddOnKey>0</AddOnKey>
                <data/>
                <parameters/>
            </table>
        </Data>
    </Data>
</Root>';

SELECT TRIM('{}' FROM p.value('(id/text())[1]', 'NVARCHAR(255)')) AS ID
    , c.value('(column[@name="ec_date"]/@value)[1]', 'NVARCHAR(255)') AS ec_date
    , c.value('(column[@name="ec_description"]/@value)[1]', 'NVARCHAR(255)') AS ec_description
    , c.value('(column[@name="ec_Type"]/@value)[1]', 'NVARCHAR(255)') AS ec_Type
    , c.value('(column[@name="ec_currency"]/@value)[1]', 'CHAR(3)') AS ec_currency
FROM @XMLData.nodes('/Root/Data/Data/table') AS t1(p)
    OUTER APPLY p.nodes('rows/row/columns') AS t2(c);

Выход

+--------------------------------------+----------------+---------------------------------+---------+-------------+
|                  ID                  |    ec_date     |         ec_description          | ec_Type | ec_currency |
+--------------------------------------+----------------+---------------------------------+---------+-------------+
| 54bd0b6f-693e-476f-a80e-0094de4cf3b4 | 27-July-2021   | Reiskosten woon-werk            | XP504   | EUR         |
| 9adfe4dd-49be-48fb-a838-009e861be8bd | 24-August-2021 | reiskosten Alkmaar-ZaandamXP502 | XP502   | EUR         |
| 9adfe4dd-49be-48fb-a838-009e861be8bd | 26-August-2021 | reiskosten Alkmaar-Zaandam      | XP502   | EUR         |
+--------------------------------------+----------------+---------------------------------+---------+-------------+