WITH xmlnamespaces
    (
        DEFAULT 'LoginomDecisionMaker'
    )
SELECT 
    t.rep_id AS 'ID',
    t.rh_eid AS 'REPORT_HEADER/eid',
    t.cra_code AS 'REPORT_HEADER/CRA_CODE',
    (
        SELECT  pd_eid.c AS 'eid',
                @x.value('(/response/items[et/text() = "PERSON_DATA" and k/text() = "CRA_CODE" and eid/text() = sql:column("pd_eid.c")]/v/text())[1]', 'nvarchar(100)') AS 'CRA_CODE',
                @x.value('(/response/items[et/text() = "PERSON_DATA" and k/text() = "name1" and eid/text() = sql:column("pd_eid.c")]/v/text())[1]', 'nvarchar(100)') AS 'name1',
                @x.value('(/response/items[et/text() = "PERSON_DATA" and k/text() = "first" and eid/text() = sql:column("pd_eid.c")]/v/text())[1]', 'nvarchar(100)') AS 'first',
                @x.value('(/response/items[et/text() = "PERSON_DATA" and k/text() = "paternal" and eid/text() = sql:column("pd_eid.c")]/v/text())[1]', 'nvarchar(100)') AS 'paternal'
        FROM    @x.nodes('/response/items[et/text() = "PERSON_DATA" and k/text() = "PARENT_ID" and v/text() = sql:column("t.rep_id")]') AS pd_id (c)
                CROSS APPLY (VALUES (pd_id.c.value('(eid/text())[1]', 'nvarchar(100)'))) AS pd_eid (c)
        FOR XML PATH ('PERSON_DATA'), TYPE
    )
FROM
    (
        SELECT DISTINCT  
            rep_id.c AS rep_id,
            rh_eid.c AS rh_eid,
            cra.c.value('(v/text())[1]', 'nvarchar(100)') AS cra_code
        FROM
            @x.nodes('/response/items[k/text() = "PARENT_ID"]') AS t(c)
            CROSS APPLY (VALUES (t.c.value('(v/text())[1]', 'nvarchar(100)'))) AS rep_id(c)
            CROSS APPLY t.c.nodes('/response/items[et/text() = "REPORT_HEADER" and k/text() = "PARENT_ID" and v/text() = sql:column("rep_id.c")]') AS rh(c)
            CROSS APPLY (VALUES (rh.c.value('(eid/text())[1]', 'nvarchar(100)'))) AS rh_eid (c)
            CROSS APPLY t.c.nodes('/response/items[et/text() = "REPORT_HEADER" and k/text() = "CRA_CODE" and eid/text() = sql:column("rh_eid.c")]') AS cra(c)
    ) AS t
for xml path('REPORT'), root('DOCUMENTS'), type