Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Export result of SQL Query to XML - Synology VideoStation metadata
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.