1. Página inicial
  2. Pergunta

Como obter valores a partir de um XML, onde os nomes são os mesmos

0

Pergunta

Eu tenho que criar uma tabela a partir de um arquivo XML.

O problema é que cada ID pode ter várias Linhas.

Assim, a tabela precisa ser assim.

ID ec_date ec_description ec_Type ec_currency
54bd0b6f-693e-476f-a80e-0094de4cf3b4 27-julho-2021 Reiskosten eu-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

Esta é a minha estrutura 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>

Até então eu tentei esta consulta:

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)

O resultado é semelhante a esse: | 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 há várias linhas ligadas a um ID-lo corretamente faz várias linhas.

O problema que eu estou tendo é que eu não consigo obter o valor de colunas XML.

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

Melhor resposta

1

Por favor, tente a solução seguinte.

Ele irá funcionar a partir do SQL Server 2017 em diante.

Notáveis pontos:

  • OUTER APPLY simula a relação pai/filho entre dois níveis no XML de entrada.
  • XQuery .nodes() método de expressões XPath foram devidamente ajustados.
  • t1(p) e t2(c) são aliases para pai /filho (um-para-muitos) relacional conjuntos de resultados.
  • Avançado TRIM(...) a função é usada para remover chaves. Essa função foi introduzido no 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);

Saída

+--------------------------------------+----------------+---------------------------------+---------+-------------+
|                  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
Este era exatamente o que eu estava procurando , obrigado.
Jelmer

Em outros idiomas

Esta página está em outros idiomas

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

Popular nesta categoria

Perguntas populares nesta categoria

Concat coluna de uma tabela para cada coluna de outra tabela
Índice em uma rápida mudança coluna
Personalizado gerado coluna de Identidade no Oracle
Como retornar mais de uma linha a partir da subconsulta usado como expressão
Como buscar cadeia depois de "=" em uma linha multi valor de coluna usando o oracle sql?
Como podemos declarar múltiplas partições em um único extrato no Postgres
Compare as duas linhas (ambos com ID diferente) & verifique se os seus valores de coluna são exatamente os mesmos. Todas as linhas e colunas da mesma tabela
Como obter dados semanais, mas a partir do primeiro dia do mês e fazer o cálculo de SOMA em conformidade, em BQ?
Posso concatenar dois coluna sql e usá-lo em função DATE_ADD
SQL select da tabela onde existirem várias entradas