Блог

Я анализирую XML-файл с помощью Snowflake.

Образец XML :

 <?xml version="1.0" encoding="utf-8"?>
<projet num_PEBN="{BCB1FA53-CE80-4357-9AE5-A672323C648A}" version="1.1.0.3" referentiel_ec="2">
    <R version="1.0.1.3" phase="5">
        <ent>
            <batiment>
                <index>1</index>
                <nom>Bâtiment B2</nom>
                <zone>
                    <index>1</index>
                    <usage>16</usage>
                    <sdp>8459.00</sdp>
                    <contributeur ref="1">
                        <lot ref="1">
                            <sous_lot ref="1">
                                <data>
                                    <id_fiche>13751</id_fiche>
                                    <id_base>0</id_base>
                                    <id_produit>INIES_DGA</id_produit>
                                    <nom>Gaines</nom>
                                    <unite_uf>12</unite_uf>
                                    <quantite>205.38217</quantite>
                                    <dve>50</dve>
                                    <type_donnees>3</type_donnees>
                                </data>
                            </sous_lot>
                        </lot>
                        <lot ref="2">
                            <data>
                                <id_fiche>13752</id_fiche>
                                <id_base>1</id_base>
                                <id_produit>INIES</id_produit>
                                <nom>Fourreaux</nom>
                                <unite_uf>11</unite_uf>
                                <quantite>205.17</quantite>
                                <dve>50</dve>
                                <type_donnees>3</type_donnees>
                            </data>                         
                        </lot>
                    </contributeur>
                </zone> 
            </batiment>
        </ent>
    </R>
</projet>
 

Как лучше всего создать представление, собирающее некоторые значения свойств «данные», когда у вас могут быть разные иерархические «уровни» ?

• батмент > зона >> участник >>> лот >>>> су-лот >>>>> данные
• батмент > зона >> участник >>> лот >>>> данные

На данный момент я использую ОБЪЕДИНЕНИЕ для 2 разных запросов, для второго случая значение «sous_lot» равно НУЛЮ :

     //CREATE OR REPLACE VIEW MyView as 
  (SELECT XMLGET(bat.value,'index'):"$"::int as bat_index,
         XMLGET(bat.value, 'nom'):"$"::string as nom_batiment,
         XMLGET(zone.value,'index'):"$"::int as zone_index,
         XMLGET(zone.value,'usage'):"$"::int as zone_usage,
         GET(contributeur.value, '@ref')::int as contributeur_ref,
         GET(lot.value, '@ref')::int as lot_ref, 
         GET(sous_lot.value, '@ref')::int as sous_lot_ref,
         XMLGET(data.value,'id_base'):"$"::int as id_base
       
  FROM FicTable as xml_table,
          LATERAL FLATTEN(to_array(XMLGET(XMLGET(xml_table.C1,'R'), 'ent'):"$")) bat,
          LATERAL FLATTEN(to_array(bat.value:"$")) zone,
          LATERAL FLATTEN(to_array(zone.value:"$")) contributeur,
          LATERAL FLATTEN(to_array(contributeur.value:"$")) lot,
          LATERAL FLATTEN(to_array(lot.value:"$")) sous_lot,
          LATERAL FLATTEN(to_array(sous_lot.value:"$")) data
          
  WHERE GET(bat.value, '@') = 'batiment'
        AND GET(zone.value, '@') = 'zone'
        AND GET(contributeur.value, '@') = 'contributeur'
        AND GET(lot.value, '@') = 'lot'
        AND GET(sous_lot.value, '@') = 'sous_lot'
        AND GET(data.value, '@') = 'data')

UNION

  (SELECT XMLGET(bat.value,'index'):"$"::int as bat_index,
         XMLGET(bat.value, 'nom'):"$"::string as nom_batiment,
         XMLGET(zone.value,'index'):"$"::int as zone_index,
         XMLGET(zone.value,'usage'):"$"::int as zone_usage,
         GET(contributeur.value, '@ref')::int as contributeur_ref,
         GET(lot.value, '@ref')::int as lot_ref, 
         NULL as sous_lot_ref,
         XMLGET(data.value,'id_base'):"$"::int as id_base
         
  FROM FicTable as xml_table,
          LATERAL FLATTEN(to_array(XMLGET(XMLGET(xml_table.C1,'R'), 'ent'):"$")) bat,
          LATERAL FLATTEN(to_array(bat.value:"$")) zone,
          LATERAL FLATTEN(to_array(zone.value:"$")) contributeur,
          LATERAL FLATTEN(to_array(contributeur.value:"$")) lot,
          LATERAL FLATTEN(to_array(lot.value:"$")) data
          
  WHERE GET(bat.value, '@') = 'batiment'
        AND GET(zone.value, '@') = 'zone'
        AND GET(contributeur.value, '@') = 'contributeur'
        AND GET(lot.value, '@') = 'lot'
        AND GET(data.value, '@') = 'data');
 

Я полагаю, что есть лучший способ сделать это, возможно, с помощью одного запроса.

Заранее спасибо за вашу помощь.