Importuj pliki XML w PostgreSQL


Naprawdę mam wiele plików XML, które chciałbym zaimportować do
XML_Data <!-- ​​Code--> Tabela: 
create table xml_data(result xml);

W tym celu mam prosty skrypt bash z cyklem: 
#!/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

Spróbuje to jednak zaimportować każdą linię każdego pliku jako oddzielnego ciągu. Prowadzi to do błędu: 
ERROR: invalid XML content

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

Rozumiem, dlaczego to się nie powiedzie, ale nie mogę dowiedzieć się, jak sprawić, aby
\ copy
zaimportował cały plik naraz w jednej linii.
2021-03-02 Dodaj komentarz
Udostępnij to

Nie znaleziono powiązanych wyników

    Zaproszony:
    Anonimowy użytkownik

    Anonimowy użytkownik

    Potwierdzenie od:

    Nekromancja:
    Dla tych, którzy potrzebują przykładu roboczego: 
    DO $$
    
 DECLARE myxml xml;
    
BEGINmyxml := 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;

    Lub z mniejszym hałasem 
    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)
    
;

    W tym przykładzie plik XML (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>

    Uwaga:

    MyData.xml musi znajdować się w katalogu PG_DATA (katalog nadrzędny pg_stat).

    Na przykład /var/lib/postgresql/9.3/min/mydata.xml
    To wymaga PostGreSQL 9.1+



    Podsumowując, możesz to osiągnąć bez takich plików: 
    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: [url=https://en.wikipedia.org/wiki/List_of_DNS_record_types]https://en.wikipedia.org/wiki/ ... types[/url]
    
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)
    
;

    Należy pamiętać, że w przeciwieństwie do MS-SQL, XPath Tekst () zwraca NULL przez NULL, a nie pusty ciąg.

    Jeśli z jakiegokolwiek powodu musisz wyraźnie sprawdzić dostępność NULL, możesz użyć
    [Nie (@xsi: nil = "true")]
    , do którego musisz przekazać tablicę przestrzeni nazw, ponieważ w Drugi przypadek otrzymasz błąd (jednak możesz pominąć wszystkie przestrzenie przestrzeni nazw, z wyjątkiem 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="[url=http://www.w3.org/2001/XMLSchema-instance">]http://www.w3.org/2001/XMLSche ... gt%3B[/url]
    
 <row>
    
 <xmlEncodeTest xsi:nil="true"/>
    
 <nixda>noob</nixda>
    
 </row>
    
 </table>
    
 ' AS xml) 
    
 )) AS myTempTable(myXmlColumn)
    
;

    Możesz również sprawdzić, czy pole jest zawarte w tekście XML, wykonując następujące kroki 
    ,xmlexists('//xmlEncodeTest[1]' PASSING BY REF myTempTable.myXmlColumn) AS c1e

    Na przykład, gdy przekazujesz wartość XML do zapisanej procedury/funkcji dla Cruda.
    (patrz wyżej)
    Ponadto zauważ, że właściwy sposób przesyłania wartości Null do XML jest & LT; ElementName XSI: Nil = "True"/& GT; , a nie
    & LT; ElementName/& >
    lub nic. Nie ma prawidłowego sposobu przesyłania null w atrybutach (możesz tylko pominąć atrybut, ale następnie staje się trudny/powoli określić liczbę kolumn i ich nazw w dużym zestawie danych).
    na przykład 
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    
<table>
    
 <row column1="a" column2="3"/>
    
 <row column1="b" column2="4" column3="true"/>
    
</table>

    (jest to bardziej kompaktowe, ale bardzo złe, jeśli musisz go zaimportować, zwłaszcza jeśli z plików XML z wieloma GB danych - zobacz świetny przykład w zrzutach danych 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', '<?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)
    2021-03-02 0 0
    Udostępnij to
    Anonimowy użytkownik

    Anonimowy użytkownik

    Potwierdzenie od:

    Spróbowałem innego podejścia: Przeczytaj plik XML bezpośrednio do zmiennej w funkcji PLPGSQL i kontynuuj stamtąd. Musi być

    o wiele szybciej

    i dużo bardziej niezawodne.
    <pre class="lang-sql prettyprint-override">
    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;
    
 ...

    Potrzebujesz praw

    superuser

    a plik powinien być

    lokalnie na serwerze

    DB, w niedrogim katalogu.

    Pełny przykład kodu z dodatkowymi objaśnieniami i linkami:
    2021-03-02 0 0
    Udostępnij to
    Anonimowy użytkownik

    Anonimowy użytkownik

    Potwierdzenie od:

    Rozwinięcie doskonałej odpowiedzi @ stefana-steigera. Oto przykład, który wyodrębnia element XML z węzłów podrzędnych zawierających wiele rodzeństwa (np. Wiele elementów
    & < synonym & >
    dla określonego węzła nadrzędnego
    & < synomyms & >
    ).
    Napotkałem ten problem z moimi danymi i od dłuższego czasu szukałem rozwiązania; jego odpowiedź była dla mnie najbardziej pomocna.

    Przykładowy plik danych,
    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>


    Na bok:

    Plik źródłowy XML miał adres URL w elemencie dokumentu 
    <hmdb xmlns="[url=http://www.hmdb.ca">]http://www.hmdb.ca">[/url]

    To uniemożliwiło XPath , aby przeanalizować dane. On

    będzie

    praca (brak komunikatów o błędach), ale relacja/tabela jest pusta: 
    [hmdb_test]# \i/mnt/Vancouver/Programming/data/hmdb/sql/hmdb_test.sql
    
DO
    
 accession | name | synonym 
    
-----------+------+---------

    Ponieważ oryginalny plik ma 3,4 GB, zdecydowałem się edytować ten wiersz za pomocą
    sed
    : 
    sed -i '2s/.*hmdb xmlns.*/<hmdb>/' hmdb_metabolites.xml

    [

    Dodawanie 2 (instruuje SED Edytuj "Linia 2") również zbieg okoliczności, w tym przypadku podwaja szybkość wykonania komendy
    SED
    .

    ]
    My Folder Data Postgre (PSQL: Pokaż dane_directory; ) 
    /mnt/Vancouver/Programming/RDB/postgres/postgres/data

    Tak więc, podobnie jak
    sudo
    , musiałem skopiować tam mój plik danych XML i
    chown
    do użytku w PostgreSQL: 
    sudo chown postgres:postgres/mnt/Vancouver/Programming/RDB/postgres/postgres/data/hmdb_metabolites_test.xml


    Skrypt ( hmdb_test.sql ): 

    DO $$DECLARE myxml xml;BEGINmyxml := 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;


    Wykonanie, wyjście (w
    PSQL
    ): 

    [hmdb_test]# \i/mnt/Vancouver/Programming/data/hmdb/hmdb_test.sqlaccession | 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]#
    2021-03-02 0 0
    Udostępnij to
    Anonimowy użytkownik

    Anonimowy użytkownik

    Potwierdzenie od:

    Użyłem
    TR
    , aby zastąpić wszystkie nowe linie przez puste linie. Spowoduje to utworzenie pliku XML tylko jedną linią. Taki plik Mogę łatwo zaimportować w jednej linii za pomocą
    Kopiuj
    .
    Oczywiście nie jest to bardzo dobry pomysł, gdy masz wartości wieloinformowane w XML. Na szczęście to nie jest mój przypadek.
    Aby zaimportować wszystkie pliki XML do folderu, możesz użyć tego skryptu bash: 
    #!/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
    2021-03-02 0 0
    Udostępnij to
    Dlaczego jest złożony? 0 odpowiedzi zostało zwiniętych

    Aby odpowiedzieć na pytania, Zaloguj się lub Zarejestruj się

    Polityka prywatności: https://zweryfikowac.pl/Polityka-prywatnosci.pdf
    Copyright © 2023, All Rights Reserved