Porównaj ceny domen i usług IT, sprzedawców z całego świata

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.
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)
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:
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]#
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

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