Hello to all
I have created a table
create TABLE TMP_BONIFICO_SEPA (
INSTRID VARCHAR2(35),
ENDTOENDID VARCHAR2(35),
CD VARCHAR2(4),
INSTDAMT NUMBER(16,2),
NM VARCHAR2(70),
CTRY VARCHAR2(2),
CTRYOFRES VARCHAR2(2),
IBAN VARCHAR2(35),
CD2 VARCHAR2(4),
USTRD VARCHAR2(140))
insert into TMP_BONIFICO_SEPA value ('1','ABB','SUPP','74.25','ABB' ,'IT','IT','IT13Q0103073','OTHR','N. 19639 del 29/04/14');
insert into TMP_BONIFICO_SEPA value ('2','DE' ,'SUPP','10308.59','DES.P.A.','IT','IT','IT36W035660160','OTHR',' N. 424877 del 22 /12/14 alla N. 424878 del 22/12/14');
insert into TMP_BONIFICO_SEPA value ('3','ESA','SUPP','8540','ESA' ,'IT','IT','IT54A030690140','OTHR',' N. 4517 del 20/0 6/14 alla N. 4518 del 20/06/14');
insert into TMP_BONIFICO_SEPA value ('4','MAC','SUPP','879.76','MAC' ,'IT','IT','IT50H0868902','OTHR',' N. 1576 del 18/1 0/14 alla N. 1779 del 22/11/14');
I need help to create a xml format (SEPA payment) as above,
SELECT XMLTYPE
(
'<?xml version="1.0" encoding="UTF-8"?>' ||
xmlElement("Foo",'Fii').getClobVal()
)
FROM DUAL ;
Using this
SELECT XMLROOT(
XMLELEMENT("PmtInf",
XMLAGG(
XMLELEMENT("CdtTrfTxInf",
XMLELEMENT ("PmtId",
XMLFOREST(
e.INSTRID AS "InstrId",
e.ENDTOENDID||' - '||e.INSTRID AS "EndToEndId" )
/* e.CD AS "CD")
e.INSTDAMT AS "INSTDAMT",
e.NM AS "NM")*/
)
)
)
)
, VERSION '1.0' ) AS employees
FROM TMP_BONIFICO_SEPA e
where e.INSTRID = '1'
I have no success,
<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2013 rel. 2 (x64) (http://www.altova.com)-->
<CBIPaymentRequest xsi:schemaLocation="urn:CBI:xsd:CBIPaymentRequest.00.04.00 CBIPaymentRequest.00.04.00.xsd" xmlns="urn:CBI:xsd:CBIPaymentRequest.00.04.00" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<GrpHdr>
<MsgId>es frn 18 02 2015</MsgId>
<CreDtTm>2015-04-24T14:28:38.3303320+02:00</CreDtTm>
<NbOfTxs>4</NbOfTxs>
<CtrlSum>19802.50</CtrlSum>
<InitgPty>
<Nm>casa psm</Nm>
<Id>
<OrgId>
<Othr>
<Id>0075821S</Id>
<Issr>CBI</Issr>
</Othr>
</OrgId>
</Id>
</InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>2015-02-18 - 24042015142838</PmtInfId>
<PmtMtd>TRF</PmtMtd>
<BtchBookg>true</BtchBookg>
<PmtTpInf>
<InstrPrty>NORM</InstrPrty>
<SvcLvl>
<Cd>SEPA</Cd>
</SvcLvl>
</PmtTpInf>
<ReqdExctnDt>2015-05-23</ReqdExctnDt>
<Dbtr>
<Nm>casa psm</Nm>
<PstlAdr>
<PstCd>30173</PstCd>
<TwnNm>VENEZIA</TwnNm>
<Ctry>IT</Ctry>
<AdrLine>VIA</AdrLine>
</PstlAdr>
<CtryOfRes>IT</CtryOfRes>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>IT12U0103002009000000123456</IBAN>
</Id>
</DbtrAcct>
<DbtrAgt>
<FinInstnId>
<BIC>PASCI123456</BIC>
<ClrSysMmbId>
<MmbId>01030</MmbId>
</ClrSysMmbId>
</FinInstnId>
</DbtrAgt>
<ChrgBr>SLEV</ChrgBr>
<CdtTrfTxInf>
<PmtId>
<InstrId>1</InstrId>
<EndToEndId>Abb. 2015-04-24 53097</EndToEndId>
</PmtId>
<PmtTpInf>
<CtgyPurp>
<Cd>SUPP</Cd>
</CtgyPurp>
</PmtTpInf>
<Amt>
<InstdAmt Ccy="EUR">74.25</InstdAmt>
</Amt>
<Cdtr>
<Nm>ABb</Nm>
<PstlAdr>
<Ctry>IT</Ctry>
</PstlAdr>
<CtryOfRes>IT</CtryOfRes>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>IT13Q0103073920000000</IBAN>
</Id>
</CdtrAcct>
<Purp>
<Cd>OTHR</Cd>
</Purp>
<RmtInf>
<Ustrd>. 19639 del 29/04/14</Ustrd>
</RmtInf>
</CdtTrfTxInf>
<CdtTrfTxInf>
<PmtId>
<InstrId>2</InstrId>
<EndToEndId>de 2015-04-24 53098</EndToEndId>
</PmtId>
<PmtTpInf>
<CtgyPurp>
<Cd>SUPP</Cd>
</CtgyPurp>
</PmtTpInf>
<Amt>
<InstdAmt Ccy="EUR">10308.59</InstdAmt>
</Amt>
<Cdtr>
<Nm>DE</Nm>
<PstlAdr>
<Ctry>IT</Ctry>
</PstlAdr>
<CtryOfRes>IT</CtryOfRes>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>IT36W0356601600000</IBAN>
</Id>
</CdtrAcct>
<Purp>
<Cd>OTHR</Cd>
</Purp>
<RmtInf>
<Ustrd>Dalla Fattura N. 424877 del 22 /12/14 alla N. 424878 del 22/12/14</Ustrd>
</RmtInf>
</CdtTrfTxInf>
<CdtTrfTxInf>
<PmtId>
<InstrId>3</InstrId>
<EndToEndId>es 2015-04-24 53099</EndToEndId>
</PmtId>
<PmtTpInf>
<CtgyPurp>
<Cd>SUPP</Cd>
</CtgyPurp>
</PmtTpInf>
<Amt>
<InstdAmt Ccy="EUR">8540</InstdAmt>
</Amt>
<Cdtr>
<Nm>es.</Nm>
<PstlAdr>
<Ctry>IT</Ctry>
</PstlAdr>
<CtryOfRes>IT</CtryOfRes>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>IT54A03069014001000</IBAN>
</Id>
</CdtrAcct>
<Purp>
<Cd>OTHR</Cd>
</Purp>
<RmtInf>
<Ustrd>Dalla Fattura N. 4517 del 20/0 6/14 alla N. 4518 del 20/06/14</Ustrd>
</RmtInf>
</CdtTrfTxInf>
<CdtTrfTxInf>
<PmtId>
<InstrId>4</InstrId>
<EndToEndId>MAc 15-04-24 53100</EndToEndId>
</PmtId>
<PmtTpInf>
<CtgyPurp>
<Cd>SUPP</Cd>
</CtgyPurp>
</PmtTpInf>
<Amt>
<InstdAmt Ccy="EUR">879.66</InstdAmt>
</Amt>
<Cdtr>
<Nm>MAx</Nm>
<PstlAdr>
<Ctry>IT</Ctry>
</PstlAdr>
<CtryOfRes>IT</CtryOfRes>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>IT50H086890200000</IBAN>
</Id>
</CdtrAcct>
<Purp>
<Cd>OTHR</Cd>
</Purp>
<RmtInf>
<Ustrd>Dalla Fattura N. 1576 del 18/1 0/14 alla N. 1779 del 22/11/14</Ustrd>
</RmtInf>
</CdtTrfTxInf>
</PmtInf>
</CBIPaymentRequest>
Could someone help me? where can I find info to do this?
Thank you all in advance