---

WITH files AS (
    SELECT
        mf.id AS file_id,
        mf.register_id,
        XMLPARSE(
            DOCUMENT convert_from(mf.bytes, 'UTF8')
        ) AS doc
    FROM dev.model_file_to_verification mf
    WHERE mf.id BETWEEN 8000 AND 8007
),
checked AS (
    SELECT
        f.file_id,
        sr.number::text AS register_number,
        (
            xpath('string(/Файл/@ВерсФорм)', f.doc)
        )[1]::text AS format_version,
        cardinality(
            xpath('/Файл/Документ/ЗапрСведПодтв', f.doc)
        ) AS request_count,
        (
            xpath(
                'string(/Файл/Документ/ЗапрСведПодтв/@НомерЗапр)',
                f.doc
            )
        )[1]::text AS xml_request_number
    FROM files f
    JOIN report.smev_register sr
        ON sr.id = f.register_id
)
SELECT
    file_id,
    register_number,
    format_version,
    request_count,
    xml_request_number,
    xml_request_number = register_number AS numbers_match
FROM checked
ORDER BY file_id;