Help needed reading XML

4

Using nodes:


DECLARE @xml xml =
'<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>';

WITH XMLNAMESPACES('urn:iso:std:iso:20022:tech:xsd:pain.001.001.03' AS ns)
SELECT 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 @xml.nodes('/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf') N(b);