Here is a possible solution which handles the "Insured Person" node in a separate query, you should run the 13 person one and get 16 rows p. person in addition to the 21 rows in the header.

😎

SELECT

ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME

,ANSWDF.DATA.value('(AL/ARSQ/RSQPL/ASQL/CSN/text())[1]','INT')AS CSN_VAL

,AWT.DATA.value('(QT/text())[1]' ,'VARCHAR(100)') AS TAG_NAME

,AWT.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE

,AWT.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE

FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)

OUTER APPLY ANSWDF.DATA.nodes('AL/AWT') AWT(DATA)

WHERE ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') IN (

'PolicyHolder Info'

,'Other Info'

,'Period of Insurance'

,'Contct Name'

)

UNION ALL

SELECT

ANSWDF.DATA.value('(T/text())[1]','VARCHAR(100)') AS NODE_NAME

,ARSQ.DATA.value('(CSN/text())[1]','INT') AS CSN_VAL

,APS.DATA.value('(../../T/text())[1]' ,'VARCHAR(100)') AS TAG_NAME

,APS.DATA.value('(DVL/DV/text())[1]','VARCHAR(100)') AS TAG_DISP_VALUE

,APS.DATA.value('(VL/V/text())[1]' ,'VARCHAR(100)') AS TAG_VALUE

FROM @TXML.nodes('AnswersDefinition/PL/AP/AL/APS') ANSWDF(DATA)

CROSS APPLY ANSWDF.DATA.nodes('AL/ARSQ/RSQPL/ASQL') ARSQ(DATA)

CROSS APPLY ARSQ.DATA.nodes('SQPL/AP/AL/APS/AL/AWT') APS(DATA);

Output

NODE_NAME CSN_VAL TAG_NAME TAG_DISP_VALUE TAG_VALUE

--------------------- ----------- ------------------------------------------ --------------- ----------------

PolicyHolder Info NULL CustomerAndInsuredPersonSocialSecurityTag 1111111111 1111111111

PolicyHolder Info NULL CustomerAndInsuredPersonFirstNameTag Mary Mary

PolicyHolder Info NULL CustomerAndInsuredPersonLastNameTag NULL NULL

PolicyHolder Info NULL CampaignCodeTag 9999 9999

PolicyHolder Info NULL AddrLine1Tag Domestic Domestic

PolicyHolder Info NULL AddrPostCodeTag AA1 1AA AA1 1AA

PolicyHolder Info NULL AddrLine3Tag Wessex Wessex

PolicyHolder Info NULL CreditScoreTag NULL NULL

PolicyHolder Info NULL StopQuoteTag NULL NULL

Contct Name NULL FirstNameTag_1 NULL NULL

Contct Name NULL LastNameTag_1 NULL NULL

Contct Name NULL TelephoneNumberTag_1 NULL NULL

Contct Name NULL TitleTag_1 NULL NULL

Contct Name NULL FirstNameTag_2 NULL NULL

Contct Name NULL LastNameTag_2 NULL NULL

Contct Name NULL TelephoneNumberTag_2 NULL NULL

Contct Name NULL TitleTag_2 NULL NULL

Other Info NULL ExternalTextTag NULL NULL

Other Info NULL InternalTextTag NULL NULL

Period of Insurance NULL StartCoverTag 2015-01-01 2015-01-01

Period of Insurance NULL EndCoverTag 2015-12-31 2015-12-31

Insurance Info 1 Insured Person 999999999999 999999999999

Insurance Info 1 Insured Person Joanne Joanne

Insurance Info 1 Insured Person Smith Smith

Insurance Info 1 Insured Person 1990-01-01 1990-01-01

Insurance Info 1 Insured Person 26 26

Insurance Info 1 Insured Person Full Full

Insurance Info 1 Insured Person Yes Yes

Insurance Info 1 Insured Person Yes Yes

Insurance Info 1 Insurance Info No No

Insurance Info 1 Insurance Info Yes Yes

Insurance Info 1 Insurance Info 1000 Sterling 1000 Sterling

Insurance Info 1 Insurance Info Yes Yes

Insurance Info 1 Insurance Info Tier 1 Tier 1

Insurance Info 1 Insurance Info No No

Insurance Info 1 Insurance Info No No

Insurance Info 2 Insured Info 888888888888 888888888888

Insurance Info 2 Insured Info Kim Kim

Insurance Info 2 Insured Info Smith Smith

Insurance Info 2 Insured Info 1970-01-01 1970-01-01

Insurance Info 2 Insured Info 46 43

Insurance Info 2 Insured Info Full Full

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info 500 Sterling 500 Sterling

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info 1000 Sterling 1000 Sterling

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info Tier 1 Tier 1

Insurance Info 2 Insured Info No No

Insurance Info 2 Insured Info Yes Yes

Insurance Info 2 Insured Info 20 Sterling 20 Sterling