1. Pagina iniziale
  2. Domanda

Come ottenere i valori da un file XML in cui i nomi sono gli stessi

0

Domanda

Devo creare una tabella da un file XML.

Il problema è che ogni ID può avere più Righe.

Il tavolo ha bisogno di guardare come questo.

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

Questa è la mia struttura 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>

Finora ho provato questa 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)

Il risultato è simile a questo: | 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 |

quando ci sono più righe collegato a un ID correttamente rende più righe.

Il problema che sto avendo è che non riesco ad ottenere il valore da colonne XML.

sql sql-server xml xquery
2021-11-23 10:07:03
1

Migliore risposta

1

Si prega di provare la soluzione seguente.

Sarà a partire da SQL Server 2017 in poi.

Punti notevoli:

  • OUTER APPLY simula la relazione padre/figlio tra due livelli di input XML.
  • XQuery .nodes() metodo di espressioni XPath sono stati regolati correttamente.
  • t1(p) e t2(c) sono alias per genitore /bambino (uno-a-molti) relazionale gruppi di risultati.
  • Avanzate TRIM(...) la funzione è utilizzato per rimuovere le parentesi graffe. Tale funzione è stata introdotta 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);

Uscita

+--------------------------------------+----------------+---------------------------------+---------+-------------+
|                  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         |
+--------------------------------------+----------------+---------------------------------+---------+-------------+
2021-11-23 16:57:17
Questo era esattamente quello che stavo cercando , grazie.
Jelmer

In altre lingue

Questa pagina è in altre lingue

Русский
..................................................................................................................
Ссылка
Polski
..................................................................................................................
Link
Română
..................................................................................................................
Legătură
한국어
..................................................................................................................
링크
हिन्दी
..................................................................................................................
लिंक
Français
..................................................................................................................
Lien
Türk
..................................................................................................................
Atıf
Česk
..................................................................................................................
Odka
Português
..................................................................................................................
Referênci
ไทย
..................................................................................................................
เชื่อมโยง
中文
..................................................................................................................
链接
Español
..................................................................................................................
Referencia
Slovenský
..................................................................................................................
Odkaz

Popolare in questa categoria

Domande popolari in questa categoria

Concat colonna di una tabella per ogni colonna di un'altra tabella
Indice in rapida evoluzione in cui la colonna
Personalizzato generato colonna di Identità in Oracle
Come restituire più di una riga dalla subquery utilizzato come espressione
Come recuperare la stringa dopo "=" un multi-linea, colonna valore di utilizzo di oracle sql?
Come possiamo dichiarare le partizioni multiple in una singola istruzione in Postgres
Confrontare due righe (sia con ID diverso) e verificare se i loro valori di colonna sono esattamente le stesse. Tutte le righe e colonne si trovano nella stessa tabella
Come ottenere i dati settimanali ma a partire dal primo giorno del mese e fare il calcolo della SOMMA di conseguenza in BQ?
Posso concatenare due colonna sql e utilizzare in funzione DATE_ADD
SQL select dalla tabella di cui esistono più voci