Importieren von XML-Dateien, die zu PostgreSQL

Ich habe eine Menge von XML-Dateien, die ich importieren möchten, die in der Tabelle xml_data:

create table xml_data(result xml);

Dazu habe ich ein einfaches bash-Skript mit Schleife:

#!/bin/sh
FILES=/folder/with/xml/files/*.xml
for f in $FILES
do
  psql psql -d mydb -h myhost -U usr -c \'\copy xml_data from $f \'
done

Allerdings wird dies versuchen zu importieren, die jede Zeile in jeder Datei als separate Zeile. Dies führt zu Fehler:

ERROR:  invalid XML content
CONTEXT:  COPY address_results, line 1, column result: "<?xml version="1.0" encoding="UTF-8"?>"

Verstehe ich, warum es ausfällt, aber kann nicht herausfinden, wie zu machen \copy zu importieren, ohne die ganze Datei auf einmal in eine einzige Zeile.

  • Nicht this thread auf der dba-Website SO helfen?
InformationsquelleAutor Tomas Greif | 2013-09-25
  1. 12

    Ich würde versuchen einen anderen Ansatz: Lesen Sie die XML-Datei direkt in variable innerhalb einer plpgsql-Funktion und gehen von dort aus. Sollte viel schneller und viel robuster.

    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;
   ...

    Müssen Sie superuser Privilegien, und die Datei muss lokal auf dem DB-server, in einem leicht zugänglichen Verzeichnis.

    Komplettes code-Beispiel mit mehr Erklärung und links:

    • Gibt es eine simiar, um einfach in der ganzen Datei ohne xpath überhaupt?
    • Es ist darauf hinzuweisen, dass diese würde nur funktionieren, auf dem server selbst, als pg_read_file liest auf dem server.
    • Ja, bemerkenswert. Die verlinkten Antwort-Staaten als viel. Ich habe eine Anmerkung, auch hier.
    InformationsquelleAutor Erwin Brandstetter
  2. 14

    Necromancing:
    Für diejenigen, die brauchen ein funktionierendes Beispiel:

    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;

    Oder mit weniger Lärm

    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)
;

    Mit diesem Beispiel-XML-Datei (MyData.xml):

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
    <record>
        <ID>1</ID>
        <Name>A</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Address record</Text>
        <Desc>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.</Desc>
    </record>
    <record>
        <ID>2</ID>
        <Name>NS</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Name server record</Text>
        <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
    </record>
</data-set>

    Beachten:

    MyData.xml muss in der PG_Data Verzeichnis (Eltern-Verzeichnis des pg_stat-Verzeichnis).

    z.B. /var/lib/postgresql/9.3/main/MyData.xml

    Dies erfordert PostGreSQL 9.1+

    Insgesamt haben, können Sie erreichen es fileless, wie diese:

    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('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
    <record>
        <ID>1</ID>
        <Name>A</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Address record</Text>
        <Desc>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.</Desc>
    </record>
    <record>
        <ID>2</ID>
        <Name>NS</Name>
        <RFC>RFC 1035[1]</RFC>
        <Text>Name server record</Text>
        <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
    </record>
</data-set>
' AS xml)   
)) AS myTempTable(myXmlColumn)
;

    Beachten Sie, dass im Gegensatz zu MS-SQL, xpath text() gibt NULL zurück, auf einen NULL-Wert, und nicht einen leeren string.

    Wenn aus irgendeinem Grund Sie brauchen, um explizit zu überprüfen, für die Existenz der NULL, die Sie verwenden können [not(@xsi:nil="true")], die Sie brauchen, um übergeben Sie ein array der namespaces, weil Sie andernfalls eine Fehlermeldung erhalten (Sie können jedoch alle namespaces weglassen, aber 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('<?xml version="1.0" encoding="utf-8"?>
    <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <row>
        <xmlEncodeTest xsi:nil="true" />
        <nixda>noob</nixda>
      </row>
    </table>
    ' AS xml)   
    )
) AS myTempTable(myXmlColumn)
;

    Können Sie auch überprüfen, ob ein Feld enthalten ist, in einem XML-text, indem Sie 

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

    zum Beispiel, wenn Sie übergeben Sie ein XML-Wert einer gespeicherten Prozedur/Funktion für CRUD.
    (siehe oben)

    Beachten Sie auch, dass die richtige Art und Weise übergeben Sie einen null-Wert in der XML ist <elementName xsi:nil="true" /> und nicht <elementName /> oder nichts. Es gibt keine richtige Art und Weise übergeben Sie den Wert NULL in Attributen (Sie können nur das Attribut weglassen, aber dann wird es schwierig/langsam zu schließen, die Anzahl der Spalten und deren Namen in ein großes dataset).

    z.B. 

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table>
    <row column1="a" column2="3" />
    <row column1="b" column2="4" column3="true" />
</table>

    (ist kompakter, aber sehr schlecht, wenn Sie brauchen, um es zu importieren, vor allem, wenn von XML-Dateien mit mehreren GB Daten - sehen Sie ein wunderbares Beispiel dafür, dass in der stackoverflow-Daten-dump)

    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', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table>
    <row column1="a" column2="3" />
    <row column1="b" column2="4" column3="true" />
</table>'
))  AS myTempTable(myXmlColumn)
    InformationsquelleAutor Stefan Steiger
  3. 5

    Ausweitung @stefan-steiger ausgezeichnete Antwort, hier ist ein Beispiel, extrahiert die XML-Elemente von der child-Knoten, die enthalten mehrere Geschwister (z.B. mehrere <synonym> Elemente, die für einen bestimmten <synomyms> übergeordneten Knoten).

    Dass ich auf diese Frage mit meinen Daten gesucht und schon einiges für eine Lösung; seine Antwort war die hilfreichste, zu mir.

    Beispiel-Datendatei, hmdb_metabolites_test.xml:

    <?xml version="1.0" encoding="UTF-8"?>
<hmdb>
<metabolite>
  <accession>HMDB0000001</accession>
  <name>1-Methylhistidine</name>
  <synonyms>
    <synonym>(2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoic acid</synonym>
    <synonym>1-Methylhistidine</synonym>
    <synonym>Pi-methylhistidine</synonym>
    <synonym>(2S)-2-amino-3-(1-Methyl-1H-imidazol-4-yl)propanoate</synonym>
  </synonyms>
</metabolite>
<metabolite>
  <accession>HMDB0000002</accession>
  <name>1,3-Diaminopropane</name>
  <synonyms>
    <synonym>1,3-Propanediamine</synonym>
    <synonym>1,3-Propylenediamine</synonym>
    <synonym>Propane-1,3-diamine</synonym>
    <synonym>1,3-diamino-N-Propane</synonym>
  </synonyms>
</metabolite>
<metabolite>
  <accession>HMDB0000005</accession>
  <name>2-Ketobutyric acid</name>
  <synonyms>
    <synonym>2-Ketobutanoic acid</synonym>
    <synonym>2-Oxobutyric acid</synonym>
    <synonym>3-Methyl pyruvic acid</synonym>
    <synonym>alpha-Ketobutyrate</synonym>
  </synonyms>
</metabolite>
</hmdb>

    Beiseite: der original-XML-Datei hatte eine URL in das Dokument-Element

    <hmdb xmlns="http://www.hmdb.ca">

    verhindert, dass xpath aus der Analyse der Daten. Es wird ausgeführt (ohne Fehlermeldungen), aber die relation/Tabelle ist leer:

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

    Da die source-Datei ist 3,4 GB, habe ich beschlossen, zu Bearbeiten, die Zeile mit sed:

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

    [Hinzufügen der 2 (weist sed Bearbeiten "Zeile 2") auch-zufällig-in diesem Beispiel-die Verdoppelung der sed Befehl die Ausführungsgeschwindigkeit.]

    Mein postgres Ordner "data" (PSQL: SHOW data_directory;) ist

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

    so, als sudo musste ich kopieren meine XML-Daten-Datei es und chown es für den Einsatz in PostgreSQL:

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

    Skript (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;

    Ausführung, Ausgang (in 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]#
    InformationsquelleAutor Victoria Stuart
  4. 0

    Ich habe tr ersetzt alle Zeilenumbrüche durch Leerzeichen. So erstellen Sie XML-Datei mit nur einer Zeile. Eine solche Datei kann ich importieren, einfach in eine Zeile mit \copy.

    Offensichtlich ist dies nicht eine gute Idee, im Fall, wo Sie multi-line-Werte in XML. Zum Glück ist das nicht mein Fall.

    Importieren alle XML-Dateien in den Ordner, den Sie verwenden können, dieses bash-Skript:

    #!/bin/sh
FILES=/folder/with/xml/files/*.xml
for f in $FILES
do
  tr '\n' ' ' < $f > temp.xml
  psql -d database -h localhost -U usr -c '\copy xml_data from temp.xml'
done
    InformationsquelleAutor Tomas Greif
Schreibe einen Kommentar