1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| USE [Dedo]
GO
/****** Object: Generation de fichier XML Script Date: 02/07/2020 10:41:01 ******/
DECLARE @XML XML;
WITH XMLNAMESPACES (default 'urn:iso:std:iso:20022:tech:xsd:pain.001.001.03')
SET @XML = (
SELECT
'VIREMENT AVOIR' "GrpHdr/MsgId"
,'2020-06-04T10:15:00' "GrpHdr/CreDtTm"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "GrpHdr/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "GrpHdr/CtrlSum"
,'TTTT' as "GrpHdr/InitgPty/Nm"
,'FR' as "GrpHdr/InitgPty/PstlAdr/Ctry"
,'15 RUE BIBI' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777 La Ville' as "GrpHdr/InitgPty/PstlAdr/AdrLine"
,'77777777777777' as "GrpHdr/InitgPty/Id/OrgId/Othr/Id"
,'FR' as "GrpHdr/InitgPty/CtryOfRes"
--
,null "PmtInf/Id"
,null "PmtInf/PmtMtd"
,null "PmtInf/BtchBookg"
,null as "PmtInf/NbOfTxs"
,null as "PmtInf/CtrlSum"
,null "PmtInf/PmtTpInf/SvcLvl/Cd"
,null as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,Null "PmtInf/Dbtr/Nm"
,null "PmtInf/DbtrAcct/Id/IBAN"
,null "PmtInf/DbtrAgt/FinInstnId/BIC"
,null as "PmtInf/CdtTrfTxInf/PmtId"
,null as "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,null as "PmtInf/CdtTrfTxInf/Amt/InstdAmt"
,null as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,null as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,null as "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
union all
select
null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,PmtInfId "PmtInf/Id"
,PmtMtd "PmtInf/PmtMtd"
,BtchBookg "PmtInf/BtchBookg"
,(select COUNT([Numéro de prise en compte]) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "PmtInf/NbOfTxs"
,(select SUM(InstdAmt) FROM [Dedo].[dbo].[v_comptants] where [Numéro de prise en compte] in ('444444')) as "PmtInf/CtrlSum"
,Cd "PmtInf/PmtTpInf/SvcLvl/Cd"
,'2020-06-09' as "PmtInf/ReqdExctnDt" -- /!\ a modifier à chaque fois /!\
,NmTcar "PmtInf/Dbtr/Nm"
,IBANTcar "PmtInf/DbtrAcct/Id/IBAN"
,BIC "PmtInf/DbtrAgt/FinInstnId/BIC"
,InstrId "PmtInf/CdtTrfTxInf/PmtId/InstrId"
,EndToEndId "PmtInf/CdtTrfTxInf/PmtId/EndToEndId"
,'Eur' as "PmtInf/CdtTrfTxInf/Amt/InstdAmt/@Ccy"
,Cast ( InstdAmt as decimal(18,2) ) as [text()]
,dbo.RemoveNonASCII(nm) as "PmtInf/CdtTrfTxInf/Cdtr/nm"
,upper(IBAN) as "PmtInf/CdtTrfTxInf/CdtrAcct/Id/IBAN"
,Ustrd "PmtInf/CdtTrfTxInf/RmtInf/Ustrd"
FROM [Dedo].[dbo].[v_comptants]
where [Numéro de prise en compte] in ('444444')
for xml path(''), root('Document')
); |