CREATE OR REPLACE PROCEDURE "sp_GenMovieXML" IS
    vMovieLib CLOB;
    vMovieLibrary XMLTYPE;
    vMovieItem XMLTYPE;
    vMovNameList XMLTYPE;
    vMovIDList XMLTYPE;
    vMovieID XMLTYPE;
BEGIN
    SELECT XMLELEMENT("movie_library")
    INTO vMovieLibrary FROM DUAL;

    FOR Movie IN
    (SELECT *
     FROM "tbl_Movie"
     ORDER BY "Year" DESC)
    LOOP
        SELECT XMLELEMENT("movie",
            XMLELEMENT("guid", LOWER("fn_GenGUID"))
        )
        INTO vMovieItem FROM DUAL;

        SELECT XMLELEMENT("name_list",
            XMLELEMENT("name", XMLATTRIBUTES(Movie."Name_1_lang" as "lang", '1' as "org"),
                Movie."Name_1"
            )
        )
        INTO vMovNameList FROM DUAL;

        IF (Movie."Name_2" IS NOT NULL) THEN
            SELECT APPENDCHILDXML(vMovNameList, 'name_list',
                XMLELEMENT("name", XMLATTRIBUTES(Movie."Name_2_lang" as "lang"),
                    Movie."Name_2"
                )
            )
            INTO vMovNameList
            FROM DUAL;
        END IF;

        IF (Movie."Name_3" IS NOT NULL) THEN
            SELECT APPENDCHILDXML(vMovNameList, 'name_list',
                XMLELEMENT("name", XMLATTRIBUTES(Movie."Name_3_lang" as "lang"),
                    Movie."Name_3"
                )
            )
            INTO vMovNameList
            FROM DUAL;
        END IF;

        IF (Movie."ID_kinopoisk" IS NOT NULL) THEN
            SELECT XMLELEMENT("id", XMLATTRIBUTES('kinopoisk' as "res"),
                Movie."ID_kinopoisk"
            )
            INTO vMovieID FROM DUAL;
        END IF;

        IF (Movie."ID_imdb" IS NOT NULL) THEN
            SELECT XMLCONCAT(vMovieID,
                XMLELEMENT("id", XMLATTRIBUTES('imdb' as "res"),
                    Movie."ID_imdb"
                )
            )
            INTO vMovieID FROM DUAL;
        END IF;

        IF (vMovieID IS NOT NULL) THEN
            SELECT XMLELEMENT("id_list", vMovieID)
            INTO vMovIDList FROM DUAL;
        END IF;

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

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

    vMovieLib := vMovieLibrary.getClobVal();

    EXECUTE IMMEDIATE('TRUNCATE TABLE "tbl_XMLClob"');

    INSERT INTO "tbl_XMLClob" ("XMLFile", "CreatedOn")
    VALUES (vMovieLib, SYSDATE);

    COMMIT;
END "sp_GenMovieXML";