SET DEFINE OFF
SET SERVEROUTPUT ON SIZE 500000

DECLARE
    vMovieLib CLOB;
    vMovieLibrary XMLTYPE;
    vMovieItem XMLTYPE;
    vMovieLine VARCHAR2(32000);
    vMovieLineLen INTEGER := 20000;
    vPosition PLS_INTEGER := 1;
    vMovieLibLen PLS_INTEGER;
BEGIN
    SELECT XMLELEMENT("movie_library") INTO vMovieLibrary FROM DUAL;

    FOR Movie IN
    (SELECT *
       FROM "tbl_Movie" mov
      WHERE mov."Link_imdb" IS NOT NULL OR mov."ID" IN (279, 322, 235)
   ORDER BY mov."Year" DESC)
    LOOP
        SELECT XMLELEMENT("movie",
            XMLELEMENT("guid", LOWER("fn_CreateGUID"))
        )
        INTO vMovieItem FROM DUAL;

        IF (Movie."Name_eng" = '-') THEN
            SELECT APPENDCHILDXML(vMovieItem, 'movie',
                XMLELEMENT("name", XMLATTRIBUTES('RU' AS "language", '1' AS "original"),
                    TRIM(Movie."Name_rus")
                )
            )
            INTO vMovieItem
            FROM DUAL;
        ELSE
            SELECT APPENDCHILDXML(vMovieItem, 'movie',
                XMLCONCAT(
                    XMLELEMENT("name", XMLATTRIBUTES(Movie."Name_eng_lang" AS "language", '1' AS "original"),
                        TRIM(Movie."Name_eng")
                    ),
                    XMLELEMENT("name", XMLATTRIBUTES('RU' AS "language"),
                        TRIM(Movie."Name_rus")
                    )
                )
            )
            INTO vMovieItem
            FROM DUAL;
        END IF;

        SELECT APPENDCHILDXML(vMovieItem, 'movie',
            XMLCONCAT(
                XMLELEMENT("year", TRIM(Movie."Year")),
                XMLELEMENT("length", TRIM(Movie."Length"))
            )
        )
        INTO vMovieItem
        FROM DUAL;

        IF (Movie."Link_kinopoisk" IS NOT NULL) THEN
            SELECT APPENDCHILDXML(vMovieItem, 'movie',
                XMLELEMENT("link", XMLATTRIBUTES('kinopoisk.ru' AS "resource"), SUBSTR(TRIM(Movie."Link_kinopoisk"), 24))
            )
            INTO vMovieItem
            FROM DUAL;
        END IF;

        IF (Movie."Link_imdb" IS NOT NULL) THEN
            SELECT APPENDCHILDXML(vMovieItem, 'movie',
                XMLELEMENT("link", XMLATTRIBUTES('imdb.com' AS "resource"), SUBSTR(TRIM(Movie."Link_imdb"), 20))
            )
            INTO vMovieItem
            FROM DUAL;
        END IF;

        SELECT APPENDCHILDXML(vMovieItem, 'movie',
            XMLELEMENT("view_count", TRIM(Movie."View_count"))
        )
        INTO vMovieItem
        FROM DUAL;

        SELECT APPENDCHILDXML(vMovieLibrary, 'movie_library', vMovieItem)
          INTO vMovieLibrary
          FROM DUAL;
    END LOOP;

    vMovieLib := vMovieLibrary.getClobVal();

    /*
    SELECT REPLACE(vMovieLib, '&amp;', '&')
    INTO vMovieLib FROM DUAL;

    SELECT REPLACE(vMovieLib, '&apos;', '''')
    INTO vMovieLib FROM DUAL;
    */

    vMovieLibLen := DBMS_LOB.GETLENGTH(vMovieLib);

    DBMS_OUTPUT.PUT_LINE('<?xml version="1.0" encoding="utf-8"?>');
    WHILE (vPosition <= vMovieLibLen)
    LOOP
        DBMS_LOB.READ(vMovieLib, vMovieLineLen, vPosition, vMovieLine);
        DBMS_OUTPUT.PUT_LINE(vMovieLine);
                
        vPosition := vPosition + vMovieLineLen;
    END LOOP;
END;