Saltar al contenido

Importar archivos XML a PostgreSQL

Luego de investigar en diferentes repositorios y páginas webs de internet al final nos hemos encontrado la respuesta que te compartiremos ahora.

Solución:

Nigromante: para aquellos que necesitan un ejemplo práctico:

DO $$
   DECLARE myxml xml;
BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS mytable;
CREATE TEMP TABLE mytable AS 

SELECT 
     (xpath('//ID/text()', x))[1]::text AS id
    ,(xpath('//Name/text()', x))[1]::text AS Name 
    ,(xpath('//RFC/text()', x))[1]::text AS RFC
    ,(xpath('//Text/text()', x))[1]::text AS Text
    ,(xpath('//Desc/text()', x))[1]::text AS Desc
FROM unnest(xpath('//record', myxml)) x
;

END$$;


SELECT * FROM mytable;

O con menos ruido

SELECT 
     (xpath('//ID/text()', myTempTable.myXmlColumn))[1]::text AS id
    ,(xpath('//Name/text()', myTempTable.myXmlColumn))[1]::text AS Name 
    ,(xpath('//RFC/text()', myTempTable.myXmlColumn))[1]::text AS RFC
    ,(xpath('//Text/text()', myTempTable.myXmlColumn))[1]::text AS Text
    ,(xpath('//Desc/text()', myTempTable.myXmlColumn))[1]::text AS Desc
    ,myTempTable.myXmlColumn as myXmlElement
FROM unnest(
    xpath
    (    '//record'
        ,XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'))
    )
) AS myTempTable(myXmlColumn)
;

Con este archivo XML de ejemplo (MyData.xml):



    
        1
        A
        RFC 1035[1]
        Address record
        Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.
    
    
        2
        NS
        RFC 1035[1]
        Name server record
        Delegates a DNS zone to use the given authoritative name servers

Nota:
MyData.xml debe estar en el directorio PG_Data (el directorio principal del directorio pg_stat).
p.ej /var/lib/postgresql/9.3/main/MyData.xml

Esto requiere PostGreSQL 9.1+

En general, puede lograrlo sin archivos, así:

SELECT 
     (xpath('//ID/text()', myTempTable.myXmlColumn))[1]::text AS id
    ,(xpath('//Name/text()', myTempTable.myXmlColumn))[1]::text AS Name 
    ,(xpath('//RFC/text()', myTempTable.myXmlColumn))[1]::text AS RFC
    ,(xpath('//Text/text()', myTempTable.myXmlColumn))[1]::text AS Text
    ,(xpath('//Desc/text()', myTempTable.myXmlColumn))[1]::text AS Desc
    ,myTempTable.myXmlColumn as myXmlElement 
    -- Source: https://en.wikipedia.org/wiki/List_of_DNS_record_types
FROM unnest(xpath('//record', 
 CAST('

    
        1
        A
        RFC 1035[1]
        Address record
        Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.
    
    
        2
        NS
        RFC 1035[1]
        Name server record
        Delegates a DNS zone to use the given authoritative name servers
    

' AS xml)   
)) AS myTempTable(myXmlColumn)
;

Tenga en cuenta que, a diferencia de MS-SQL, xpath text () devuelve NULL en un valor NULL, y no en un vacío. string.
Si por alguna razón necesita verificar explícitamente la existencia de NULL, puede usar [not(@xsi:nil="true")], a la que debe pasar una array de espacios de nombres, porque de lo contrario, obtendrá un error (sin embargo, puede omitir todos los espacios de nombres excepto xsi).

SELECT 
     (xpath('//xmlEncodeTest[1]/text()', myTempTable.myXmlColumn))[1]::text AS c1

    ,(
    xpath('//xmlEncodeTest[1][not(@xsi:nil="true")]/text()', myTempTable.myXmlColumn
    ,
    ARRAY[
        -- ARRAY['xmlns','http://www.w3.org/1999/xhtml'], -- defaultns
        ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance'],
        ARRAY['xsd','http://www.w3.org/2001/XMLSchema'],        
        ARRAY['svg','http://www.w3.org/2000/svg'],
        ARRAY['xsl','http://www.w3.org/1999/XSL/Transform']
    ]
    )
    )[1]::text AS c22


    ,(xpath('//nixda[1]/text()', myTempTable.myXmlColumn))[1]::text AS c2 
    --,myTempTable.myXmlColumn as myXmlElement
    ,xmlexists('//xmlEncodeTest[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1e
    ,xmlexists('//nixda[1]' PASSING BY REF myTempTable.myXmlColumn) AS c2e
    ,xmlexists('//xmlEncodeTestAbc[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1ea
FROM unnest(xpath('//row', 
     CAST('
    
        
        noob
      

      
    

    ' AS xml)   
    )
) AS myTempTable(myXmlColumn)
;

También puede verificar si un campo está contenido en un texto XML, haciendo 

 ,xmlexists('//xmlEncodeTest[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1e

por ejemplo, cuando pasa un valor XML a una función / procedimiento almacenado para CRUD. (véase más arriba)

Además, tenga en cuenta que la forma correcta de pasar un null-valor en XML es y no o nada. No hay una forma correcta de pasar NULL en attributes (solo puedes omitir el attribute, pero luego se vuelve difícil / lento inferir el número de columnas y sus nombres en un conjunto de datos grande).

p.ej

(es más compacto, pero muy malo si necesita importarlo, especialmente si es de archivos XML con varios GB de datos; vea un ejemplo maravilloso de eso en el volcado de datos de stackoverflow)

SELECT 
     myTempTable.myXmlColumn
    ,(xpath('//@column1', myTempTable.myXmlColumn))[1]::text AS c1
    ,(xpath('//@column2', myTempTable.myXmlColumn))[1]::text AS c2
    ,(xpath('//@column3', myTempTable.myXmlColumn))[1]::text AS c3
    ,xmlexists('//@column3' PASSING BY REF myTempTable.myXmlColumn) AS c3e
    ,case when (xpath('//@column3', myTempTable.myXmlColumn))[1]::text is null then 1 else 0 end AS is_null 
FROM unnest(xpath('//row', '

    


    
'
))  AS myTempTable(myXmlColumn)

Intentaría un enfoque diferente: leer el archivo XML directamente en la variable dentro de una función plpgsql y continuar desde allí. Debiera ser mucho mas rapido y mucho más robusto.

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'path/to/my_file.xml';
BEGIN
   myxml := pg_read_file(datafile, 0, 100000000);  -- arbitrary 100 MB max.

   CREATE TEMP TABLE tmp AS
   SELECT (xpath('//some_id/text()', x))[1]::text AS id
   FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;
   ...

Necesitas superusuario privilegios, y el archivo debe ser local al servidor de base de datos, en un directorio accesible.
Ejemplo de código completo con más explicaciones y enlaces:

  • Datos XML a la base de datos PostgreSQL

Ampliando la excelente respuesta de @ stefan-steiger, aquí hay un ejemplo que extrae elementos XML de los nodos secundarios que contienen varios hermanos (por ejemplo, varios elementos, para un particular nodo padre).

Encontré este problema con mis datos y busqué bastante una solución; su respuesta fue la más útil para mí.

Archivo de datos de ejemplo, hmdb_metabolites_test.xml:




  HMDB0000001
  1-Methylhistidine
  
    (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoic acid
    1-Methylhistidine
    Pi-methylhistidine
    (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoate
  


  HMDB0000002
  1,3-Diaminopropane
  
    1,3-Propanediamine
    1,3-Propylenediamine
    Propane-1,3-diamine
    1,3-diamino-N-Propane
  


  HMDB0000005
  2-Ketobutyric acid
  
    2-Ketobutanoic acid
    2-Oxobutyric acid
    3-Methyl pyruvic acid
    alpha-Ketobutyrate

Aparte: el archivo XML original tenía una URL en el elemento de documento

eso impidió xpath analizando los datos. Eso voluntad ejecutar (sin mensajes de error), pero la relación / tabla está vacía:

[hmdb_test]# i /mnt/Vancouver/Programming/data/hmdb/sql/hmdb_test.sql
DO
 accession | name | synonym 
-----------+------+---------

Dado que el archivo fuente es de 3.4GB, decidí editar esa línea usando sed:

sed -i '2s/.*hmdb xmlns.*//' hmdb_metabolites.xml

[Adding the 2 (instructs sed to edit “line 2”) also — coincidentally, in this instance — doubling the sed command execution speed.]

Mi carpeta de datos de postgres (PSQL: SHOW data_directory;) es

/mnt/Vancouver/Programming/RDB/postgres/postgres/data

así como sudo, Necesitaba copiar mi archivo de datos XML allí y chown para usarlo en PostgreSQL:

sudo chown postgres:postgres /mnt/Vancouver/Programming/RDB/postgres/postgres/data/hmdb_metabolites_test.xml

Texto (hmdb_test.sql):

DO $$DECLARE myxml xml;

BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('hmdb_metabolites_test.xml'), 'UTF8'));

DROP TABLE IF EXISTS mytable;

-- CREATE TEMP TABLE mytable AS 
CREATE TABLE mytable AS 
SELECT 
    (xpath('//accession/text()', x))[1]::text AS accession
    ,(xpath('//name/text()', x))[1]::text AS name 
    -- The "synonym" child/subnode has many sibling elements, so we need to
    -- "unnest" them,otherwise we only retrieve the first synonym per record:
    ,unnest(xpath('//synonym/text()', x))::text AS synonym
FROM unnest(xpath('//metabolite', myxml)) x
;

END$$;

-- select * from mytable limit 5;
SELECT * FROM mytable;

Ejecución, salida (en PSQL):

[hmdb_test]# i /mnt/Vancouver/Programming/data/hmdb/hmdb_test.sql

accession  |        name        |                         synonym                          
-------------+--------------------+----------------------------------------------------------
HMDB0000001 | 1-Methylhistidine  | (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoic acid
HMDB0000001 | 1-Methylhistidine  | 1-Methylhistidine
HMDB0000001 | 1-Methylhistidine  | Pi-methylhistidine
HMDB0000001 | 1-Methylhistidine  | (2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoate
HMDB0000002 | 1,3-Diaminopropane | 1,3-Propanediamine
HMDB0000002 | 1,3-Diaminopropane | 1,3-Propylenediamine
HMDB0000002 | 1,3-Diaminopropane | Propane-1,3-diamine
HMDB0000002 | 1,3-Diaminopropane | 1,3-diamino-N-Propane
HMDB0000005 | 2-Ketobutyric acid | 2-Ketobutanoic acid
HMDB0000005 | 2-Ketobutyric acid | 2-Oxobutyric acid
HMDB0000005 | 2-Ketobutyric acid | 3-Methyl pyruvic acid
HMDB0000005 | 2-Ketobutyric acid | alpha-Ketobutyrate

[hmdb_test]#

Puedes secundar nuestra ocupación dejando un comentario o dejando una puntuación te lo agradecemos.

¡Haz clic para puntuar esta entrada!
(Votos: 2 Promedio: 4.5)

Tags : / / /

Utiliza Nuestro Buscador

Preguntas Relacionadas:

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *