All geek questions in one place

OPENXML imports xml into ms sql - namespace

I used this site for a long time, and many times it helped me in solving various problems. This time I'm stuck. I am trying to import complex xml like the one below in the ms-sql table.

<?xml version='1.0' encoding='UTF-8'?>
<S2SCTScf:SCTScfBlkCredTrf xmlns="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf" xmlns:S2SCTScf="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf SCTScfBlkCredTrf.xsd">
  <S2SCTScf:SndgInst>XXXXXXXX</S2SCTScf:SndgInst>
  <S2SCTScf:RcvgInst>YYYYYYYY</S2SCTScf:RcvgInst>
  <S2SCTScf:SrvcId>SCT</S2SCTScf:SrvcId>
  <S2SCTScf:TstCode>P</S2SCTScf:TstCode>
  <S2SCTScf:FType>SCF</S2SCTScf:FType>
  <S2SCTScf:FileRef>AAAAAAAAAAAAAAAAAAA</S2SCTScf:FileRef>
  <S2SCTScf:RoutingInd>IND</S2SCTScf:RoutingInd>
  <S2SCTScf:FileBusDt>2016-11-01</S2SCTScf:FileBusDt>
  <S2SCTScf:FileCycleNo>01</S2SCTScf:FileCycleNo>
  <S2SCTScf:FIToFICstmrCdtTrf xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.02">
    <GrpHdr>
      <MsgId>111111111111111111</MsgId>
      <CreDtTm>2016-11-01T15:45:11.0Z</CreDtTm>
      <NbOfTxs>11</NbOfTxs>
      <TtlIntrBkSttlmAmt Ccy="EUR">111111</TtlIntrBkSttlmAmt>
      <IntrBkSttlmDt>2016-11-01</IntrBkSttlmDt>
      <SttlmInf>
        <SttlmMtd>CLRG</SttlmMtd>
        <ClrSys>
          <Prtry>ST2</Prtry>
        </ClrSys>
      </SttlmInf>
      <InstgAgt>
        <FinInstnId>
          <BIC>XXXXXXXX</BIC>
        </FinInstnId>
      </InstgAgt>
      <InstdAgt>
        <FinInstnId>
          <BIC>XXXXXXXX</BIC>
        </FinInstnId>
      </InstdAgt>
    </GrpHdr>
    <CdtTrfTxInf>
      <PmtId>
        <EndToEndId>NOTPROVIDED</EndToEndId>
        <TxId>XXXXXXXXXXXXXXXXXXXXXXXXXX</TxId>
      </PmtId>
      <PmtTpInf>
        <SvcLvl>
          <Cd>SEPA</Cd>
        </SvcLvl>
      </PmtTpInf>
      <IntrBkSttlmAmt Ccy="XXX">1.00</IntrBkSttlmAmt>
      <ChrgBr>SLEV</ChrgBr>
      <Dbtr>
        <Nm>MXXXXXX XXXXXXX</Nm>
        <PstlAdr>
          <Ctry>XX</Ctry>
          <AdrLine>XXXXXXXXXXXXXXXXXXXXXXXXXXX</AdrLine>
        </PstlAdr>
      </Dbtr>
      <DbtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </DbtrAcct>
      <DbtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXX</BIC>
        </FinInstnId>
      </DbtrAgt>
      <CdtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXX</BIC>
        </FinInstnId>
      </CdtrAgt>
      <Cdtr>
        <Nm>XXXXXXXXXXXXXXXXXXXXXXX</Nm>
      </Cdtr>
      <CdtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </CdtrAcct>
      <RmtInf>
        <Ustrd>XXXXXXXXXXXXXXXXXXXXXXX</Ustrd>
      </RmtInf>
    </CdtTrfTxInf>
    <CdtTrfTxInf>
      <PmtId>
        <EndToEndId>NOTPROVIDED</EndToEndId>
        <TxId>XXXXXXXXXXXXXXXXXXXXXXXXXXXX</TxId>
      </PmtId>
      <PmtTpInf>
        <SvcLvl>
          <Cd>SEPA</Cd>
        </SvcLvl>
      </PmtTpInf>
      <IntrBkSttlmAmt Ccy="XXX">1.00</IntrBkSttlmAmt>
      <ChrgBr>SLEV</ChrgBr>
      <Dbtr>
        <Nm>XXXXXXXXXXXXXXXXX</Nm>
        <PstlAdr>
          <Ctry>XX</Ctry>
          <AdrLine>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</AdrLine>
        </PstlAdr>
      </Dbtr>
      <DbtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </DbtrAcct>
      <DbtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXXXXXXXXXXXXXXX</BIC>
        </FinInstnId>
      </DbtrAgt>
      <CdtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXXXXXXXXXXXXXXX</BIC>
        </FinInstnId>
      </CdtrAgt>
      <Cdtr>
        <Nm>XXXXXXXXXXXXXXXXXXXXXXXX</Nm>
      </Cdtr>
      <CdtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </CdtrAcct>
      <RmtInf>
        <Ustrd>XXXXXXXXXXXXXXXXXXXXXXXXXXXXX</Ustrd>
      </RmtInf>
    </CdtTrfTxInf>
  </S2SCTScf:FIToFICstmrCdtTrf>
</S2SCTScf:SCTScfBlkCredTrf>
Run codeHide result

I am trying to use OPENXML as well as XQuery functions, but I am having some problems declaring and using namespaces (or namespaceuri). I am not familiar with such complex xml and namespaceuri. I need an idea to get the data below the table. I managed to use simpler xml, even with one namespace. I manually deleted the first 11 lines, and the selection below works very well ...

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML where id=6 --this is the xml without first 11 lines
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT * FROM OPENXML(@hDoc, 'FIToFICstmrCdtTrf/CdtTrfTxInf')
  WITH 
  (
    CCY [varchar](100) 'IntrBkSttlmAmt/@Ccy',
    IntrBkSttlmAmt [varchar](100) 'IntrBkSttlmAmt',
    TxId [varchar](100) 'PmtId/TxId',
    EndToEndId [varchar](100) 'PmtId/EndToEndId',
    ChrgBr [varchar](100) 'ChrgBr'
    --etc
  )
EXEC sp_xml_removedocument @hDoc
GO
Run codeHide result
+4
1

  • FROM OPENXML ( )
  • XML <?xml version=''1.0'' encoding=''UTF-8''?>. VARCHAR -path, . utf-16 NVARCHAR -path. "N" XML-.
  • ... ... , , *: .
  • : S2SCTScf:FIToFICstmrCdtTrf . innerDeflt.

DECLARE @xml XML=
'<?xml version=''1.0'' encoding=''UTF-8''?>
<S2SCTScf:SCTScfBlkCredTrf xmlns="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf" xmlns:S2SCTScf="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf SCTScfBlkCredTrf.xsd">
  <S2SCTScf:SndgInst>XXXXXXXX</S2SCTScf:SndgInst>
  <S2SCTScf:RcvgInst>YYYYYYYY</S2SCTScf:RcvgInst>
  <S2SCTScf:SrvcId>SCT</S2SCTScf:SrvcId>
  <S2SCTScf:TstCode>P</S2SCTScf:TstCode>
  <S2SCTScf:FType>SCF</S2SCTScf:FType>
  <S2SCTScf:FileRef>AAAAAAAAAAAAAAAAAAA</S2SCTScf:FileRef>
  <S2SCTScf:RoutingInd>IND</S2SCTScf:RoutingInd>
  <S2SCTScf:FileBusDt>2016-11-01</S2SCTScf:FileBusDt>
  <S2SCTScf:FileCycleNo>01</S2SCTScf:FileCycleNo>
  <S2SCTScf:FIToFICstmrCdtTrf xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.02">
    <GrpHdr>
      <MsgId>111111111111111111</MsgId>
      <CreDtTm>2016-11-01T15:45:11.0Z</CreDtTm>
      <NbOfTxs>11</NbOfTxs>
      <TtlIntrBkSttlmAmt Ccy="EUR">111111</TtlIntrBkSttlmAmt>
      <IntrBkSttlmDt>2016-11-01</IntrBkSttlmDt>
      <SttlmInf>
        <SttlmMtd>CLRG</SttlmMtd>
        <ClrSys>
          <Prtry>ST2</Prtry>
        </ClrSys>
      </SttlmInf>
      <InstgAgt>
        <FinInstnId>
          <BIC>XXXXXXXX</BIC>
        </FinInstnId>
      </InstgAgt>
      <InstdAgt>
        <FinInstnId>
          <BIC>XXXXXXXX</BIC>
        </FinInstnId>
      </InstdAgt>
    </GrpHdr>
    <CdtTrfTxInf>
      <PmtId>
        <EndToEndId>NOTPROVIDED</EndToEndId>
        <TxId>XXXXXXXXXXXXXXXXXXXXXXXXXX</TxId>
      </PmtId>
      <PmtTpInf>
        <SvcLvl>
          <Cd>SEPA</Cd>
        </SvcLvl>
      </PmtTpInf>
      <IntrBkSttlmAmt Ccy="XXX">1.00</IntrBkSttlmAmt>
      <ChrgBr>SLEV</ChrgBr>
      <Dbtr>
        <Nm>MXXXXXX XXXXXXX</Nm>
        <PstlAdr>
          <Ctry>XX</Ctry>
          <AdrLine>XXXXXXXXXXXXXXXXXXXXXXXXXXX</AdrLine>
        </PstlAdr>
      </Dbtr>
      <DbtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </DbtrAcct>
      <DbtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXX</BIC>
        </FinInstnId>
      </DbtrAgt>
      <CdtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXX</BIC>
        </FinInstnId>
      </CdtrAgt>
      <Cdtr>
        <Nm>XXXXXXXXXXXXXXXXXXXXXXX</Nm>
      </Cdtr>
      <CdtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </CdtrAcct>
      <RmtInf>
        <Ustrd>XXXXXXXXXXXXXXXXXXXXXXX</Ustrd>
      </RmtInf>
    </CdtTrfTxInf>
    <CdtTrfTxInf>
      <PmtId>
        <EndToEndId>NOTPROVIDED</EndToEndId>
        <TxId>XXXXXXXXXXXXXXXXXXXXXXXXXXXX</TxId>
      </PmtId>
      <PmtTpInf>
        <SvcLvl>
          <Cd>SEPA</Cd>
        </SvcLvl>
      </PmtTpInf>
      <IntrBkSttlmAmt Ccy="XXX">1.00</IntrBkSttlmAmt>
      <ChrgBr>SLEV</ChrgBr>
      <Dbtr>
        <Nm>XXXXXXXXXXXXXXXXX</Nm>
        <PstlAdr>
          <Ctry>XX</Ctry>
          <AdrLine>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</AdrLine>
        </PstlAdr>
      </Dbtr>
      <DbtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </DbtrAcct>
      <DbtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXXXXXXXXXXXXXXX</BIC>
        </FinInstnId>
      </DbtrAgt>
      <CdtrAgt>
        <FinInstnId>
          <BIC>XXXXXXXXXXXXXXXXXXXXXXXX</BIC>
        </FinInstnId>
      </CdtrAgt>
      <Cdtr>
        <Nm>XXXXXXXXXXXXXXXXXXXXXXXX</Nm>
      </Cdtr>
      <CdtrAcct>
        <Id>
          <IBAN>XXXXXXXXXXXXXXXXXXXXXXXXXX</IBAN>
        </Id>
      </CdtrAcct>
      <RmtInf>
        <Ustrd>XXXXXXXXXXXXXXXXXXXXXXXXXXXXX</Ustrd>
      </RmtInf>
    </CdtTrfTxInf>
  </S2SCTScf:FIToFICstmrCdtTrf>
</S2SCTScf:SCTScfBlkCredTrf>';

: . 1:1, , , XPath. <CdtTrfTxInf> , 1:n-approach APPLY .nodes().

, XML. .

WITH XMLNAMESPACES(DEFAULT  'urn:S2SCTScf:xsd:$SCTScfBlkCredTrf'
                           ,'urn:S2SCTScf:xsd:$SCTScfBlkCredTrf' as S2SCTScf
                           ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi
                           ,'urn:S2SCTScf:xsd:$SCTScfBlkCredTrf SCTScfBlkCredTrf.xsd' AS schemaLocation
                           ,'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.02' AS innerDeflt)
SELECT rt.value(N'(S2SCTScf:SndgInst)[1]','nvarchar(max)') AS SndgInst
      ,rt.value(N'(S2SCTScf:RcvgInst)[1]','nvarchar(max)') AS RcvgInst
      --more like this
      ,rt.value(N'(S2SCTScf:FIToFICstmrCdtTrf/innerDeflt:GrpHdr/innerDeflt:MsgId)[1]','nvarchar(max)') AS MsgId
      ,rt.value(N'(S2SCTScf:FIToFICstmrCdtTrf/innerDeflt:GrpHdr/innerDeflt:CreDtTm)[1]','datetime') AS CreDtTm
      --more like this
      ,rt.value(N'(S2SCTScf:FIToFICstmrCdtTrf/innerDeflt:GrpHdr/innerDeflt:TtlIntrBkSttlmAmt/@Ccy)[1]','nvarchar(max)') AS TtlIntrBkSttlmAmt_Ccy
      ,rt.value(N'(S2SCTScf:FIToFICstmrCdtTrf/innerDeflt:GrpHdr/innerDeflt:TtlIntrBkSttlmAmt)[1]','int') AS TtlIntrBkSttlmAmt
      --all nodes are 1:1, just "more of the same"
      --But CdtTrfTxInf is there twice, therefore the call to OUTER APPLY rt.nodes()
      ,cti.value(N'(innerDeflt:PmtId/innerDeflt:EndToEndId)[1]','nvarchar(max)') AS EndToEndId
      --all the rest is following the same schema...
FROM @xml.nodes(N'S2SCTScf:SCTScfBlkCredTrf') AS A(rt) --root
OUTER APPLY rt.nodes(N'S2SCTScf:FIToFICstmrCdtTrf/innerDeflt:CdtTrfTxInf') AS B(cti) --CdtTrfTxInf

( ...)

SndgInst    RcvgInst    MsgId               CreDtTm     TtlIntrBkSttlmAmt_Ccy   TtlIntrBkSttlmAmt   EndToEndId
XXXXXXXX    YYYYYYYY    111111111111111111  2016-11-01 15:45:11.000 EUR 111111  NOTPROVIDED
XXXXXXXX    YYYYYYYY    111111111111111111  2016-11-01 15:45:11.000 EUR 111111  NOTPROVIDED
+1

Source: https://habr.com/ru/post/1667271/

More articles:


All Articles