How to get values from a XML where the names are the same

I have to create a table from a XML file.

The catch is that every ID can have multiple Rows.

So the table needs to look like this.

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-Zaandam	XP502	EUR
9adfe4dd-49be-48fb-a838-009e861be8bd	26-August-2021	reiskosten Alkmaar-Zaandam	XP502	EUR

This is my XML structure:

<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-Zaandam" 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>

So far I've tried this query:

DECLARE @XMLData XML = ('<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 top 2000
    ID = id.value('(table/id)[1]', 'nvarchar(255)'),
    ec_date = ColumnData.value('Column[1]', 'nvarchar(255)')
    ec_description = ColumnData.value('Column[2]', 'nvarchar(255)')


FROM
    @XMLData.nodes('/Root/Data/Data') AS XTbl(id)
CROSS APPLY
    id.nodes('table/rows/row/columns') AS XTbl2(ColumnData)

The result is like this: | ID | ec_date | ec_description | | -------- | -------- | -------- | | 54bd0b6f-693e-476f-a80e-0094de4cf3b4 | NULL | NULL | | 9adfe4dd-49be-48fb-a838-009e861be8bd | NULL | NULL | | 9adfe4dd-49be-48fb-a838-009e861be8bd | NULL | NULL |

when there are multiple rows linked to an ID it correctly makes multiple rows.

The problem I'm having is that I can't seem to get the value from the XML columns.

Solution

Please try the following solution.

It will will work starting from SQL Server 2017 onwards.

Notable points:

OUTER APPLY simulates parent/child relationship between two levels in the input XML.
XQuery .nodes() method XPath expressions were properly adjusted.
t1(p) and t2(c) are aliases for parent /child (one-to-many) relational resultsets.
Advanced TRIM(...) function is used to remove curly brackets. That function was introduced in 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);

Output

+--------------------------------------+----------------+---------------------------------+---------+-------------+
|                  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         |
+--------------------------------------+----------------+---------------------------------+---------+-------------+