Reading XML

Transact-SQL
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);