Help needed reading XML

1

I have several legacy xml documents held as string values in a SQL database. I have so far been unable to read the required values from these. I have tried select cast xx as xml but am getting stuck on retrieving the actual data. I am looking to get the values from Nm, InstdAmt, MmbId, Id and Ustrd. Can anyone point me in the right direction

<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>
2

Name spaces in XML are a pain so I would be inclined to remove xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" before converting to XML. The value method will probably then behave as you expect.

3

Just had a quick look at the code and actually got this to work with the xmlns:

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 @xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:Dbtr/ns:Nm)[1]', 'varchar(20)') AS Nm
	,@xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf/ns:Amt/ns:InstdAmt/@Ccy)[1]', 'char(3)') AS Ccy
	,@xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf/ns:Amt/ns:InstdAmt)[1]', 'money') AS InstdAmt
	,@xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf/ns:CdtrAgt/ns:FinInstnId/ns:ClrSysMmbId/ns:MmbId)[1]', 'int') AS MmbId
	,@xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf/ns:CdtrAcct/ns:Id/ns:Othr/ns:Id)[1]', 'int') AS Id
	,@xml.value('(/ns:Document/ns:CstmrCdtTrfInitn/ns:PmtInf/ns:CdtTrfTxInf/ns:RmtInf/ns:Ustrd)[1]', 'varchar(20)') AS Ustrd
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);