Trebuie să creați un tabel dintr-un fișier XML.
Captura este că fiecare ID-ul poate avea mai multe Rânduri.
Deci tabelul trebuie să arate așa.
ID | ec_date | ec_description | ec_Type | ec_currency |
---|---|---|---|---|
54bd0b6f-693e-476f-a80e-0094de4cf3b4 | 27-iulie-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 |
Acest lucru este meu de structura 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-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>
Până acum am încercat această interogare:
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)
Rezultatul este ca aceasta: | 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 |
atunci când există mai multe randuri legate de un ID corect face mai multe rânduri.
Problema mea este că eu nu pot par pentru a obține valoarea de XML coloane.