Skip to main content

How to Unnest XML Data

Lattes​

Suppose you have an XML file like the one below and you want to flatten the data so it becomes easier to analyze.

XML example​

 <root>
    <src_xml DATA-ATUALIZACAO="2022-02-24" HORA-ATUALIZACAO="12:00:00" NUMERO-IDENTIFICADOR="123" SISTEMA-ORIGEM-XML="SistemaXYZ">
        <PRODUCAO-BIBLIOGRAFICA>
            <ARTIGOS-ACEITOS-PARA-PUBLICACAO>
                <DADOS-BASICOS-DO-ARTIGO ANO-DO-ARTIGO="2021" DOI="..." FLAG-DIVULGACAO-CIENTIFICA="NAO" ...>
                    <!-- outros atributos de DADOS-BASICOS-DO-ARTIGO -->
                </DADOS-BASICOS-DO-ARTIGO>
                <DETALHAMENTO-DO-ARTIGO FASCICULO="..." ISSN="..." ...>
                    <!-- outros atributos de DETALHAMENTO-DO-ARTIGO -->
                </DETALHAMENTO-DO-ARTIGO>
            </ARTIGOS-ACEITOS-PARA-PUBLICACAO>
            <!-- outros elementos de PRODUCAO-BIBLIOGRAFICA -->
        </PRODUCAO-BIBLIOGRAFICA>
    </src_xml>
</root>

How to unnest it​

Select the main attributes​

SELECT 
    src_xml,
    get(src_xml, '@DATA-ATUALIZACAO') as data_atualizacao,
    get(src_xml, '@HORA-ATUALIZACAO') as hora_atualizacao,
    get(src_xml, '@NUMERO-IDENTIFICADOR') as lattes_id,
    get(src_xml, '@SISTEMA-ORIGEM-XML') as sistema_origem_xml,
FROM 
    sua_tabela

Here, we are selecting the main attributes directly from the src_xml node. The get function extracts attribute values.

Unnest the basic article data​

get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DADOS-BASICOS-DO-ARTIGO'), '@ANO-DO-ARTIGO') as dados_basicos_ano,
get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DADOS-BASICOS-DO-ARTIGO'), '@DOI') as dados_basicos_doi,

Here, xmlget accesses nested nodes inside src_xml, and get extracts specific attributes from those nested nodes.

Unnest the article details​

get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DETALHAMENTO-DO-ARTIGO'), '@FASCICULO') as detalhamento_fasciculo,
get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DETALHAMENTO-DO-ARTIGO'), '@ISSN') as detalhamento_issn

The same approach is used here to extract specific attributes from the DETALHAMENTO-DO-ARTIGO node.

At the end of the process, the complete query looks like this:

SELECT 
    src_xml,
    get(src_xml, '@DATA-ATUALIZACAO') as data_atualizacao,
    get(src_xml, '@HORA-ATUALIZACAO') as hora_atualizacao,
    get(src_xml, '@NUMERO-IDENTIFICADOR') as lattes_id,
    get(src_xml, '@SISTEMA-ORIGEM-XML') as sistema_origem_xml,
    get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DADOS-BASICOS-DO-ARTIGO'), '@ANO-DO-ARTIGO') as dados_basicos_ano,
    get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DADOS-BASICOS-DO-ARTIGO'), '@DOI') as dados_basicos_doi,
    get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DETALHAMENTO-DO-ARTIGO'), '@FASCICULO') as detalhamento_fasciculo,
    get(xmlget(src_xml, 'PRODUCAO-BIBLIOGRAFICA/ARTIGOS-ACEITOS-PARA-PUBLICACAO/DETALHAMENTO-DO-ARTIGO'), '@ISSN') as detalhamento_issn
FROM 
    sua_tabela

In summary, this SQL query navigates the structure of an XML field called src_xml inside sua_tabela and extracts the attributes you need. get and xmlget are used to traverse the XML hierarchy and return the values for each attribute you want to expose. This is a generic example, so adjust it to match the actual XML structure and database schema you are working with.