Export result of SQL Query to XML - Synology VideoStation metadata

export_vsmeta_to_xml.sql

CREATE TEMP TABLE "videodata_tmp" AS
        SELECT 
                v.mapper_id as id, 
                'home'||v.mapper_id as uniqueid,
                right(v.path, position('/' in reverse(v.path)) - 1) as filename, 
                v.path, 
                g2.genre, 
                h.title, 
                h.record_time, 
                s.summary 
                FROM video_file AS v 
                NATURAL LEFT OUTER JOIN (
                        SELECT string_agg(g1.gnere, '|') as genre, 
                        mapper_id FROM (SELECT gnere, mapper_id from gnere) as g1 
                        GROUP BY mapper_id) as g2 
                LEFT JOIN home_video AS h ON h.mapper_id = v.mapper_id 
                LEFT OUTER JOIN summary AS s ON s.mapper_id = v.mapper_id;
\copy (SELECT table_to_xml('videodata_tmp', false, false, '')) to program 'sed -e ''s/\\n//g'' > /tmp/videodata.xml';
DROP TABLE videodata_tmp;