Bir XML dosyasından bir tablo oluşturmam gerekiyor.
Yakalama, her kimliğin birden çok satıra sahip olabilmesidir.
Yani masanın böyle görünmesi gerekiyor.
KİMLİK | ec_date | ec_description | ec_Type | ec_currency |
---|---|---|---|---|
54bd0b6f-693e-476f-a80e-0094de4cf3b4 | 27-Temmuz-2021 | Reiskosten woon-werk | XP504 | EUR |
9adfe4dd-49be-48fb-a838-009e861be8bd | 24-Ağustos-2021 | reiskosten Alkmaar-Zaandam | XP502 | EUR |
9adfe4dd-49be-48fb-a838-009e861be8bd | 26-Ağustos-2021 | reiskosten Alkmaar-Zaandam | XP502 | EUR |
Bu benim XML yapım:
<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>
Şimdiye kadar bu sorguyu denedim:
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)
Sonuç şu şekildedir: / ID / ec_date / ec_description | | -------- | -------- | -------- | | 54bd0b6f-693e-476f-a80e-0094de4cf3b4 / BOŞ / BOŞ | | 9adfe4dd-49be-48fb-a838-009e861be8bd / BOŞ / BOŞ | | 9adfe4dd-49be-48fb-a838-009e861be8bd / BOŞ / BOŞ |
bir kimliğe bağlı birden çok satır olduğunda, doğru şekilde birden çok satır oluşturur.
Yaşadığım sorun, XML sütunlarından değeri alamıyor gibi görünmem.