Reading XML

Transact-SQL
1

I have been referencing previous post's trying to read this XMl file supplied by a customer. Although i get various results from different methods, I cannot get it right.
Best I can come up with only returns 1 record, and the file may contain an unknown number of records

Code I get to work

Declare @XML as XML = (Select REPLACE([ImportFile], 'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"', '') FROM [ImportFiles]  where ID = 'C75696BA-14CB-443A-9BBF-35F8148682A2') 
SELECT 
 X.XMLData.value('(//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAgt/FinInstnId/ClrSysMmbId/MmbId)[1]','varchar(6)') AS SortCode,
 X.XMLData.value('(//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/CdtrAcct/Id/Othr/Id)[1]','varchar(8)') AS AccountNumber,
 X.XMLData.value('(//Document/CstmrCdtTrfInitn/PmtInf/Dbtr/Nm)[1]','varchar(18)') AS AccountName,
 X.XMLData.value('(//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/Amt/InstdAmt)[1]','varchar(18)') AS 'Value',
 X.XMLData.value('(//Document/CstmrCdtTrfInitn/PmtInf/CdtTrfTxInf/RmtInf/Ustrd )[1]','varchar(18)') AS Reference
FROM (VALUES(@XML))X(XMLData)

XML Data

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
    <PmtInf>
      <CdtTrfTxInf>
        <Amt>
          <InstdAmt Ccy="GBP">456.29</InstdAmt>
        </Amt>
        <CdtrAgt>
          <FinInstnId>
            <ClrSysMmbId>
              <MmbId>111111</MmbId>
            </ClrSysMmbId>
          </FinInstnId>
        </CdtrAgt>
        <Cdtr>
          <Nm>COMPANY A</Nm>
        </Cdtr>
        <CdtrAcct>
          <Id>
            <Othr>
              <Id>12121212</Id>
            </Othr>
          </Id>
        </CdtrAcct>
        <RmtInf>
          <Ustrd>INVOICE TESTJ0147277 </Ustrd>
        </RmtInf>
      </CdtTrfTxInf>
      <CdtTrfTxInf>
        <Amt>
          <InstdAmt Ccy="GBP">2055.00</InstdAmt>
        </Amt>
        <CdtrAgt>
          <FinInstnId>
            <ClrSysMmbId>
              <MmbId>333333</MmbId>
            </ClrSysMmbId>
          </FinInstnId>
        </CdtrAgt>
        <Cdtr>
          <Nm>COMPANY B</Nm>
        </Cdtr>
        <CdtrAcct>
          <Id>
            <Othr>
              <Id>44444444</Id>
            </Othr>
          </Id>
        </CdtrAcct>
        <RmtInf>
          <Ustrd>INVOICE TESTJ02772TWO</Ustrd>
        </RmtInf>
      </CdtTrfTxInf>
    </PmtInf>
  </CstmrCdtTrfInitn>
</Document>
2 
/* *** Test Data *** */
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE dbo.ImportFiles
(
	ID uniqueidentifier NOT NULL PRIMARY KEY
	,ImportFile nvarchar(MAX) NOT NULL
);
INSERT INTO dbo.ImportFiles
VALUES ('C75696BA-14CB-443A-9BBF-35F8148682A2','<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
    <PmtInf>
      <Dbtr>
        <Nm>PAYER COMPANY</Nm>
      </Dbtr>
      <CdtTrfTxInf>
        <Amt>
          <InstdAmt Ccy="GBP">1.00</InstdAmt>
        </Amt>
        <CdtrAgt>
          <FinInstnId>
            <ClrSysMmbId>
              <MmbId>123456</MmbId>
            </ClrSysMmbId>
          </FinInstnId>
        </CdtrAgt>
        <CdtrAcct>
          <Id>
            <Othr>
              <Id>12429468</Id>
            </Othr>
          </Id>
        </CdtrAcct>
        <RmtInf>
          <Ustrd>Billing</Ustrd>
        </RmtInf>
      </CdtTrfTxInf>
    </PmtInf>
  </CstmrCdtTrfInitn>
</Document>');
/* *** End Test Data *** */

WITH XMLNAMESPACES('urn:iso:std:iso:20022:tech:xsd:pain.001.001.03' AS ns)
SELECT F.ID
	,N.b.value('(../ns:Dbtr/ns:Nm)[1]', 'varchar(20)') AS Nm
	,N.b.value('(./ns:Amt/ns:InstdAmt/@Ccy)[1]', 'char(3)') AS Ccy
	,N.b.value('(./ns:Amt/ns:InstdAmt)[1]', 'money') AS InstdAmt
	,N.b.value('(./ns:CdtrAgt/ns:FinInstnId/ns:ClrSysMmbId/ns:MmbId)[1]', 'int') AS MmbId
	,N.b.value('(./ns:CdtrAcct/ns:Id/ns:Othr/ns:Id)[1]', 'int') AS Id
	,N.b.value('(./ns:RmtInf/ns:Ustrd)[1]', 'varchar(20)') AS Ustrd
FROM ImportFiles F
	CROSS APPLY
	(
		VALUES ( CAST(F.ImportFile AS xml) )
	) X (FileXml)
	CROSS APPLY X.FileXml.nodes('/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf') N(b);