1. Maison
  2. La question

Comment obtenir les valeurs à partir d'un XML où les noms sont les mêmes

0

La question

J'ai créer une table à partir d'un fichier XML.

Le hic, c'est que tous les ID peuvent avoir plusieurs Lignes.

De sorte que le tableau doit ressembler à ceci.

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

C'est ma structure 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>

Jusqu'à présent, j'ai essayé cette requête:

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)

Le résultat ressemble à ceci: | 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 |

quand il y a plusieurs lignes lié à un IDENTIFIANT correctement fait plusieurs lignes.

Le problème que je vais avoir, c'est que je n'arrive pas à obtenir la valeur de la colonne XML.

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

La meilleure réponse

1

S'il vous plaît essayez la solution suivante.

Il travaillera à partir de SQL Server à partir de 2017.

Points importants:

  • OUTER APPLY simule la relation parent/enfant entre deux niveaux dans le XML d'entrée.
  • XQuery .nodes() méthode des expressions XPath ont été correctement ajustés.
  • t1(p) et t2(c) sont des alias pour parent /enfant (un-à-plusieurs) relationnel jeux de résultats.
  • Avancé TRIM(...) la fonction est utilisée pour supprimer les accolades. Cette fonction a été introduite dans 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);

Sortie

+--------------------------------------+----------------+---------------------------------+---------+-------------+
|                  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
C'est exactement ce que je cherchais , merci.
Jelmer

Dans d'autres langues

Cette page est dans d'autres langues

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

Populaires dans cette catégorie

Questions populaires dans cette catégorie

Concat colonne d'une table pour chaque colonne d'une autre table
Indice de changement rapide de la colonne
Personnalisé généré colonne d'Identité dans Oracle
Comment renvoyer plus d'une ligne de sous-requête utilisé comme expression
Comment extraire de la chaîne après le "=" dans un multi en ligne de la valeur de la colonne à l'aide de oracle sql?
Comment pouvons-nous déclarer plusieurs partitions en une seule instruction dans Postgres
Comparer les deux lignes (à la fois avec des ID différents) et vérifier si leurs valeurs de la colonne sont exactement les mêmes. Toutes les lignes et colonnes sont dans le même tableau
Comment obtenir des données hebdomadaires, mais à partir de la première date du mois et n'calcul de la SOMME en conséquence en BQ?
Je peux concaténer deux sql de la colonne et de l'utiliser en fonction DATE_ADD
SQL select à partir de la table où de multiples entrées existent