Home » SQL & PL/SQL » SQL & PL/SQL » XML parse (19c)
| XML parse [message #686928] |
Fri, 03 February 2023 07:51  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Hello
Please does anybody know how to parse this xml ?
I would like to get this xml into oracle table, I just wonder how to do that right.
<?xml version="1.0" encoding="utf-8"?>
<BizData
xmlns="urn:iso:std:iso:20022:tech:xsd:head.003.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.003.001.01 head.003.001.01.xsd">
<Hdr>
<AppHdr
xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.001.001.01 head.001.001.01_ESMA_restricted.xsd">
<Fr>
<OrgId>
<Id>
<OrgId>
<Othr>
<Id>TRDTI</Id>
<SchmeNm>
<Prtry>TRACE User Account</Prtry>
</SchmeNm>
</Othr>
</OrgId>
</Id>
</OrgId>
</Fr>
<To>
<OrgId>
<Id>
<OrgId>
<Othr>
<Id>ESMAS</Id>
<SchmeNm>
<Prtry>TRACE User Account</Prtry>
</SchmeNm>
</Othr>
</OrgId>
</Id>
</OrgId>
</To>
<BizMsgIdr>R00079-230125_001001-0</BizMsgIdr>
<MsgDefIdr>auth.030.001.01</MsgDefIdr>
<BizSvc>DATATR</BizSvc>
<CreDt>2023-01-25T21:59:00Z</CreDt>
</AppHdr>
</Hdr>
<Pyld>
<Document
xmlns="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01 auth.030.001.01_ESMAUG_DATATR_1.5.0.xsd">
<DerivsTradRpt>
<RptHdr>
<QryExctnDt>2023-01-25</QryExctnDt>
<MsgPgntn>
<PgNb>00001</PgNb>
<LastPgInd>true</LastPgInd>
</MsgPgntn>
<NbRcrds>3477</NbRcrds>
</RptHdr>
<TradData>
<Rpt>
<Tx>
<PosCmpnt>
<CtrPtySpcfcData>
<CtrPty>
<RptgCtrPty>
<Id>
<LEI>549345765765725BLF67</LEI>
</Id>
<Ntr>
<FI>CDTI</FI>
</Ntr>
<TradgCpcty>PRIN</TradgCpcty>
<CtrPtySd>BYER</CtrPtySd>
</RptgCtrPty>
<OthrCtrPty>
<Id>
<LEI>31545675735PN732</LEI>
</Id>
<Ctry>SK</Ctry>
</OthrCtrPty>
<SubmitgAgt>
<LEI>54933453525IBLF67</LEI>
</SubmitgAgt>
<ClrMmb>
<LEI>5456757575P4890</LEI>
</ClrMmb>
<Bnfcry>
<LEI>549303453543BLF67</LEI>
</Bnfcry>
</CtrPty>
<RptgDtTm>2023-01-25T10:05:40Z</RptgDtTm>
</CtrPtySpcfcData>
<CmonTradData>
<CtrctData>
<CtrctTp>FUTR</CtrctTp>
<AsstClss>EQUI</AsstClss>
<PdctClssfctn>
<ClssfctnFinInstrm>FFICSX</ClssfctnFinInstrm>
</PdctClssfctn>
<UndrlygInstrm>
<Indx>
<Nm>CNX NIFTY (INDIA) INDEX</Nm>
</Indx>
</UndrlygInstrm>
<NtnlCcyFrstLeg>USD</NtnlCcyFrstLeg>
<DlvrblCcy>USD</DlvrblCcy>
</CtrctData>
<TxData>
<UnqTradIdr>000MACQ000547657575LF671531951653</UnqTradIdr>
<RptTrckgNb>0325979596</RptTrckgNb>
<TradgVn>XSIM</TradgVn>
<Cmprssn>false</Cmprssn>
<Pric>
<MntryVal>
<Amt Ccy="USD">18.5</Amt>
</MntryVal>
</Pric>
<NtnlAmt>
<Amt>3634370</Amt>
</NtnlAmt>
<PricMltplr>2</PricMltplr>
<Qty>
<Unit>10</Unit>
</Qty>
<DlvryTp>CASH</DlvryTp>
<ExctnDtTm>2023-01-23T17:09:00Z</ExctnDtTm>
<FctvDt>2023-01-24</FctvDt>
<MtrtyDt>2023-01-25</MtrtyDt>
<TermntnDt>2023-01-23</TermntnDt>
<SttlmDt>2023-01-25</SttlmDt>
<TradConf>
<NonConfd>
<Tp>NCNF</Tp>
</NonConfd>
</TradConf>
<TradClr>
<ClrOblgtn>UKWN</ClrOblgtn>
<ClrSts>
<Clrd>
<CCP>
<LEI>5456757575252K3F0FW61</LEI>
</CCP>
<ClrDtTm>2023-01-23T17:09:00Z</ClrDtTm>
</Clrd>
</ClrSts>
<IntraGrp>false</IntraGrp>
</TradClr>
</TxData>
</CmonTradData>
</PosCmpnt>
</Tx>
</Rpt>
</TradData>
</DerivsTradRpt>
</Document>
</Pyld>
</BizData>
Martin
|
|
|
|
|
|
| Re: XML parse [message #686931 is a reply to message #686929] |
Fri, 03 February 2023 09:04  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
|
I do not have not any oracle table yet, just I would like to know how to read this xml.
|
|
|
|
|
|
| Re: XML parse [message #686933 is a reply to message #686928] |
Fri, 03 February 2023 15:28 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would help to know where your data comes from, how you retrieve it, how it is stored, and what sort of table you want to load it into. In the following I have assumed that it is in a text file on your server. I suggest that you start with the basics and test it, then work top to bottom, adding one column at a time, re-testing after you add each column. I have added a bunch of columns and left it for you to finish adding the rest. I have just done this with a select statement, but you can easily add "CREATE TABLE AS" in front of the select statement to create a table.
The basics:
SCOTT@orcl_12.1.0.2.0> -- your provided data is on my server in file c:\my_oracle_files\test.dat
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_12.1.0.2.0> SELECT *
2 FROM XMLTABLE
3 (xmlnamespaces
4 (default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
5 'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
6 'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"),
7 'BizData'
8 PASSING XMLTYPE (BFILENAME ('MY_DIR', 'test.dat'), NLS_CHARSET_ID ('AL32UTF8')))
9 /
COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------------------
<BizData xmlns="urn:iso:std:iso:20022:tech:xsd:head.003.001.01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:
tech:xsd:head.003.001.01 head.003.001.01.xsd"><Hdr><AppHdr xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-in
stance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.001.001.01 head.001.001.01_ESMA_restricted.xsd"><Fr><OrgId><Id><OrgId><Othr><Id>TRDTI</Id><Schme
Nm><Prtry>TRACE User Account</Prtry></SchmeNm></Othr></OrgId></Id></OrgId></Fr><To><OrgId><Id><OrgId><Othr><Id>ESMAS</Id><SchmeNm><Prtry>TRACE User Account</Prt
ry></SchmeNm></Othr></OrgId></Id></OrgId></To><BizMsgIdr>R00079-230125_001001-0</BizMsgIdr><MsgDefIdr>auth.030.001.01</MsgDefIdr><BizSvc>DATATR</BizSvc><CreDt>2
023-01-25T21:59:00Z</CreDt></AppHdr></Hdr><Pyld><Document xmlns="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01" xmlns:xsi="http://www.w3.org/2001/XMLSchema-ins
tance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01 auth.030.001.01_ESMAUG_DATATR_1.5.0.xsd"><DerivsTradRpt><RptHdr><QryExctnDt>2023-01-25
</QryExctnDt><MsgPgntn><PgNb>00001</PgNb><LastPgInd>true</LastPgInd></MsgPgntn><NbRcrds>3477</NbRcrds></RptHdr><TradData><Rpt><Tx><PosCmpnt><CtrPtySpcfcData><Ct
rPty><RptgCtrPty><Id><LEI>549345765765725BLF67</LEI></Id><Ntr><FI>CDTI</FI></Ntr><TradgCpcty>PRIN</TradgCpcty><CtrPtySd>BYER</CtrPtySd></RptgCtrPty><OthrCtrPty>
<Id><LEI>31545675735PN732</LEI></Id><Ctry>SK</Ctry></OthrCtrPty><SubmitgAgt><LEI>54933453525IBLF67</LEI></SubmitgAgt><ClrMmb><LEI>5456757575P4890</LEI></ClrMmb>
<Bnfcry><LEI>549303453543BLF67</LEI></Bnfcry></CtrPty><RptgDtTm>2023-01-25T10:05:40Z</RptgDtTm></CtrPtySpcfcData><CmonTradData><CtrctData><CtrctTp>FUTR</CtrctTp
><AsstClss>EQUI</AsstClss><PdctClssfctn><ClssfctnFinInstrm>FFICSX</ClssfctnFinInstrm></PdctClssfctn><UndrlygInstrm><Indx><Nm>CNX NIFTY (INDIA) INDEX</Nm></Indx>
</UndrlygInstrm><NtnlCcyFrstLeg>USD</NtnlCcyFrstLeg><DlvrblCcy>USD</DlvrblCcy></CtrctData><TxData><UnqTradIdr>000MACQ000547657575LF671531951653</UnqTradIdr><Rpt
TrckgNb>0325979596</RptTrckgNb><TradgVn>XSIM</TradgVn><Cmprssn>false</Cmprssn><Pric><MntryVal><Amt Ccy="USD">18.5</Amt></MntryVal></Pric><NtnlAmt><Amt>3634370</
Amt></NtnlAmt><PricMltplr>2</PricMltplr><Qty><Unit>10</Unit></Qty><DlvryTp>CASH</DlvryTp><ExctnDtTm>2023-01-23T17:09:00Z</ExctnDtTm><FctvDt>2023-01-24</FctvDt><
MtrtyDt>2023-01-25</MtrtyDt><TermntnDt>2023-01-23</TermntnDt><SttlmDt>2023-01-25</SttlmDt><TradConf><NonConfd><Tp>NCNF</Tp></NonConfd></TradConf><TradClr><ClrOb
lgtn>UKWN</ClrOblgtn><ClrSts><Clrd><CCP><LEI>5456757575252K3F0FW61</LEI></CCP><ClrDtTm>2023-01-23T17:09:00Z</ClrDtTm></Clrd></ClrSts><IntraGrp>false</IntraGrp><
/TradClr></TxData></CmonTradData></PosCmpnt></Tx></Rpt></TradData></DerivsTradRpt></Document></Pyld></BizData>
1 row selected.
SCOTT@orcl_12.1.0.2.0> SELECT *
2 FROM XMLTABLE
3 (xmlnamespaces
4 (default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
5 'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
6 'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"),
7 'BizData'
8 PASSING XMLTYPE (BFILENAME ('MY_DIR', 'test.dat'), NLS_CHARSET_ID ('AL32UTF8'))
9 COLUMNS
10 FrId VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:Fr/n:OrgId/n:Id/n:OrgId/n:Othr/n:Id',
11 FrPrtry VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:Fr/n:OrgId/n:Id/n:OrgId/n:Othr/n:SchmeNm/n:Prtry',
12 ToId VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:To/n:OrgId/n:Id/n:OrgId/n:Othr/n:Id',
13 ToPrtry VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:To/n:OrgId/n:Id/n:OrgId/n:Othr/n:SchmeNm/n:Prtry',
14 BizMsgIdr VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:BizMsgIdr',
15 MsgDefIdr VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:MsgDefIdr',
16 BizSvc VARCHAR2( 7) PATH '//Hdr/n:AppHdr/n:BizSvc',
17 CreDt VARCHAR2(20) PATH '//Hdr/n:AppHdr/n:CreDt',
18 QryExctnDt VARCHAR2(10) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:QryExctnDt',
19 PgNb VARCHAR2( 5) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:MsgPgntn/o:PgNb')
20 /
FRID FRPRTRY TOID TOPRTRY BIZMSGIDR MSGDEFIDR BIZSVC CREDT QRYEXCTNDT PGNB
----- ------------------ ----- ------------------ ------------------ ------------------ ------- -------------------- ---------- -----
TRDTI TRACE User Account ESMAS TRACE User Account R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001
1 row selected.
|
|
|
|
| Re: XML parse [message #686934 is a reply to message #686933] |
Sat, 04 February 2023 04:53 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
XML is loaded into XMLTYPE column type in the table.
I tried your query and is perfect, works fine withou any error.
Thank you very much Barbara, I appreciate your excelent skills.
Best regards
Martin
|
|
|
|
| Re: XML parse [message #686950 is a reply to message #686934] |
Tue, 07 February 2023 03:29 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Barbara, I try to parse another one value from xml (LEI) and I get error:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
Could you please take a look at that what Im doing wrong ??
LEI VARCHAR2(4000) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:TradData/o:Rpt/o:Tx/o:ValtnUpd/o:CtrPtySpcfcData/o:CtrPty/o:RptgCtrPty/o:Id/o:LEI'
Thanks
|
|
|
|
|
|
| Re: XML parse [message #686952 is a reply to message #686950] |
Tue, 07 February 2023 05:10 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are multiple LEI in your data, so you need to provide a path that is sufficient to tell it which one. In the sample data that you provided there is no ValtnUpd tag. However, replacing that with PosCmpnt, which is in your data, produces correct rsults.
'//Pyld/o:Document/o:DerivsTradRpt/o:TradData/o:Rpt/o:Tx/o:PosCmpnt/o:CtrPtySpcfcData/o:CtrPty/o:RptgCtrPty/o:Id/o:LEI'
You can also use the following instead, given the sample data that you provided
'//Pyld/o:Document//o:RptgCtrPty//o:LEI'
and you could use the following for the next LEI in your sample data, providing a different column name, like LEI2.
'//Pyld/o:Document//o:OthrCtrPty//o:LEI'
However, if I use
'//Pyld/o:Document//o:LEI'
with the data that you provided, then it produces the error that you got, because it cannot tell which of the previous two LEI you want.
Apparently the data you are testing on now is different from what you posted, so if you cannot figure out the correct path, then I would need to see the complete data and code that produces the error.
|
|
|
|
| Re: XML parse [message #686953 is a reply to message #686952] |
Tue, 07 February 2023 06:25 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
I would like to parse LEI values from xml, see below.
There is a ValtnUpd tag.
Can you please take a look at this a find the right solution ??
<?xml version="1.0" encoding="utf-8"?>
<BizData
xmlns="urn:iso:std:iso:20022:tech:xsd:head.003.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.003.001.01 head.003.001.01.xsd">
<Hdr>
<AppHdr
xmlns="urn:iso:std:iso:20022:tech:xsd:head.001.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.001.001.01 head.001.001.01_ESMA_restricted.xsd">
<Fr>
<OrgId>
<Id>
<OrgId>
<Othr>
<Id>TRDTI</Id>
<SchmeNm>
<Prtry>TRACE User Account</Prtry>
</SchmeNm>
</Othr>
</OrgId>
</Id>
</OrgId>
</Fr>
<To>
<OrgId>
<Id>
<OrgId>
<Othr>
<Id>ESMAS</Id>
<SchmeNm>
<Prtry>TRACE User Account</Prtry>
</SchmeNm>
</Othr>
</OrgId>
</Id>
</OrgId>
</To>
<BizMsgIdr>R00079-230125_001001-0</BizMsgIdr>
<MsgDefIdr>auth.030.001.01</MsgDefIdr>
<BizSvc>DATATR</BizSvc>
<CreDt>2023-01-25T21:59:00Z</CreDt>
</AppHdr>
</Hdr>
<Pyld>
<Document
xmlns="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:auth.030.001.01 auth.030.001.01_ESMAUG_DATATR_1.5.0.xsd">
<DerivsTradRpt>
<RptHdr>
<QryExctnDt>2023-01-25</QryExctnDt>
<MsgPgntn>
<PgNb>00001</PgNb>
<LastPgInd>true</LastPgInd>
</MsgPgntn>
<NbRcrds>3477</NbRcrds>
</RptHdr>
<TradData>
<Rpt>
<Tx>
<ValtnUpd>
<CtrPtySpcfcData>
<CtrPty>
<RptgCtrPty>
<Id>
<LEI>549300E5ENQVY2IBLF67</LEI>
</Id>
</RptgCtrPty>
<OthrCtrPty>
<Id>
<LEI>529900BJGD0X650NVB68</LEI>
</Id>
</OthrCtrPty>
<SubmitgAgt>
<LEI>549300E5ENQVY2IBLF67</LEI>
</SubmitgAgt>
</CtrPty>
<Valtn>
<CtrctVal>
<Amt Ccy="EUR">1122771.26</Amt>
<Sgn>false</Sgn>
</CtrctVal>
<TmStmp>2023-01-23T01:50:44Z</TmStmp>
<Tp>MTMA</Tp>
</Valtn>
<RptgDtTm>2023-01-25T04:13:20Z</RptgDtTm>
</CtrPtySpcfcData>
<CmonTradData>
<CtrctData>
<AsstClss>COMM</AsstClss>
<UndrlygInstrm>
<IdNotAvlbl>UKWN</IdNotAvlbl>
</UndrlygInstrm>
</CtrctData>
<TxData>
<UnqTradIdr>E5ENQVY2IBMTS-1F7A8F7</UnqTradIdr>
<Pric>
<PdgPric>PNDG</PdgPric>
</Pric>
<TradClr>
<ClrOblgtn>UKWN</ClrOblgtn>
<ClrSts>
<NonClrd>NORE</NonClrd>
</ClrSts>
</TradClr>
</TxData>
</CmonTradData>
</ValtnUpd>
</Tx>
</Rpt>
<Rpt>
<Tx>
<ValtnUpd>
<CtrPtySpcfcData>
<CtrPty>
<RptgCtrPty>
<Id>
<LEI>R0MUWSFPU8MPRO8K5P83</LEI>
</Id>
</RptgCtrPty>
<OthrCtrPty>
<Id>
<LEI>5493001GWWCBB16Z7U26</LEI>
</Id>
</OthrCtrPty>
<SubmitgAgt>
<LEI>R0MUWSFPU8MPRO8K5P83</LEI>
</SubmitgAgt>
</CtrPty>
<Coll>
<Collstn>PRCL</Collstn>
<Prtfl>171672</Prtfl>
<VartnMrgnPstd Ccy="USD">490000</VartnMrgnPstd>
<InitlMrgnRcvd Ccy="USD">0</InitlMrgnRcvd>
<VartnMrgnRcvd Ccy="USD">0</VartnMrgnRcvd>
<XcssCollPstd Ccy="USD">0</XcssCollPstd>
<XcssCollRcvd Ccy="USD">0</XcssCollRcvd>
</Coll>
<RptgDtTm>2023-01-25T01:52:54Z</RptgDtTm>
</CtrPtySpcfcData>
<CmonTradData>
<CtrctData>
<UndrlygInstrm>
<IdNotAvlbl>UKWN</IdNotAvlbl>
</UndrlygInstrm>
</CtrctData>
<TxData>
<Pric>
<PdgPric>PNDG</PdgPric>
</Pric>
<TradClr>
<ClrOblgtn>UKWN</ClrOblgtn>
</TradClr>
</TxData>
</CmonTradData>
</ValtnUpd>
</Tx>
</Rpt>
</TradData>
</DerivsTradRpt>
</Document>
</Pyld>
</BizData>
https://drive.google.com/file/d/1xphb0XrYY7l41mqgyrtMNHGhmhpCYkzl/view?usp=sharing
thanks a lot
[Updated on: Tue, 07 February 2023 12:00] Report message to a moderator |
|
|
|
| Re: XML parse [message #686956 is a reply to message #686953] |
Tue, 07 February 2023 13:48 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are two issues here.
You have multiple LEI under different categories, so those can be given different column names, just as the different Id were named FrId and ToId.
You have multiple Rpt under TradData, so you need an additional XMLTABLE. You need to store the TradData as XMLTYPE in the original XMLTABLE, then pass that to the second XMLTABLE to parse out the RPT.
Please see the demonstration below, in which I used your test.xml file from the link you provided and copied it to my server.
SCOTT@orcl_12.1.0.2.0> -- your provided data is on my server in file c:\my_oracle_files\test.xml
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_12.1.0.2.0> SELECT x.FrId, x.FrPrtry, x.ToId, x.ToPrtry, x.BizmsgIdr, x.MsgDefIdr, x.BizSvc, x.CreDt, x.QryExctnDt, x.PgNb,
2 x.LastPgInd, x.NbRcrds, y.*
3 FROM XMLTABLE
4 (xmlnamespaces
5 (default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
6 'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
7 'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"),
8 'BizData'
9 PASSING XMLTYPE (BFILENAME ('MY_DIR', 'test.xml'), NLS_CHARSET_ID ('AL32UTF8'))
10 COLUMNS
11 FrId VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:Fr/n:OrgId/n:Id/n:OrgId//n:Id',
12 FrPrtry VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:Fr//n:Prtry',
13 ToId VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:To/n:OrgId/n:Id/n:OrgId//n:Id',
14 ToPrtry VARCHAR2( 5) PATH '//Hdr/n:AppHdr/n:To//n:Prtry',
15 BizMsgIdr VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:BizMsgIdr',
16 MsgDefIdr VARCHAR2(18) PATH '//Hdr/n:AppHdr/n:MsgDefIdr',
17 BizSvc VARCHAR2( 7) PATH '//Hdr/n:AppHdr/n:BizSvc',
18 CreDt VARCHAR2(20) PATH '//Hdr/n:AppHdr/n:CreDt',
19 QryExctnDt VARCHAR2(10) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:QryExctnDt',
20 PgNb VARCHAR2( 5) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:MsgPgntn/o:PgNb',
21 LastPgInd VARCHAR2( 5) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:MsgPgntn/o:LastPgInd',
22 NbRcrds VARCHAR2( 5) PATH '//Pyld/o:Document/o:DerivsTradRpt/o:RptHdr/o:NbRcrds',
23 TradData XMLTYPE PATH '//Pyld/o:Document/o:DerivsTradRpt/o:TradData') x,
24 XMLTABLE
25 (xmlnamespaces
26 (default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
27 'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
28 'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"),
29 '/o:TradData/o:Rpt'
30 PASSING x.TradData
31 COLUMNS
32 RCPLEI VARCHAR2(20) PATH '//o:RptgCtrPty/o:Id/o:LEI',
33 OCPLEI VARCHAR2(20) PATH '//o:OthrCtrPty/o:Id/o:LEI',
34 SALEI VARCHAR2(20) PATH '//o:SubmitgAgt/o:LEI') y
35 /
FRID FRPRT TOID TOPRT BIZMSGIDR MSGDEFIDR BIZSVC CREDT QRYEXCTNDT PGNB LASTP NBRCR
----- ----- ----- ----- ------------------ ------------------ ------- -------------------- ---------- ----- ----- -----
RCPLEI OCPLEI SALEI
-------------------- -------------------- --------------------
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
R0MUWSFPU8MPRO8K5P83 5493001GWWCBB16Z7U26 R0MUWSFPU8MPRO8K5P83
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 315700NQ3QCBRX5PN732 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300U97W9KLUG0V074 529900M6JY6PUZ9NTA71 549300U97W9KLUG0V074
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
315700BW3K8BT08B9523 549300E5ENQVY2IBLF67 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 3157002QHSAD4PO4S253 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
3TK20IVIUJ8J3ZU0QE75 097900BEHZ0000001466 3TK20IVIUJ8J3ZU0QE75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700R8RHSF8AEUMW93 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
R0MUWSFPU8MPRO8K5P83 529900BJGD0X650NVB68 R0MUWSFPU8MPRO8K5P83
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 315700A46JZI3D2X8866 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
315700MFBMHTVDX4MS09 549300E5ENQVY2IBLF67 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700D4T94UM0KB8W37 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700VQHU18FVOTE904 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
1VUV7VQFKUOQSJ21A208 549300OY1IT4IB353V97 1VUV7VQFKUOQSJ21A208
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 315700FCJHLFJICRKH18 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300U97W9KLUG0V074 529900M6JY6PUZ9NTA71 549300U97W9KLUG0V074
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
R0MUWSFPU8MPRO8K5P83 529900BJGD0X650NVB68 R0MUWSFPU8MPRO8K5P83
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
DZZ47B9A52ZJ6LT6VV95 213800HB4W97MRILUI87 DZZ47B9A52ZJ6LT6VV95
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
R0MUWSFPU8MPRO8K5P83 529900BJGD0X650NVB68 R0MUWSFPU8MPRO8K5P83
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700Y2D60G5YRVRE80 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 3157002QHSAD4PO4S253 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700EGAEN1OFJ3O526 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
KR6LSKV3BTSJRD41IF75 315700ZR3SG1LKTW5569 KR6LSKV3BTSJRD41IF75
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 315700G6TNW7OST5UZ37 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 315700NQ3QCBRX5PN732 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
TRDTI TRACE ESMAS TRACE R00079-230125_0010 auth.030.001.01 DATATR 2023-01-25T21:59:00Z 2023-01-25 00001 true 3477
R0MUWSFPU8MPRO8K5P83 529900BJGD0X650NVB68 R0MUWSFPU8MPRO8K5P83
31 rows selected.
|
|
|
|
| Re: XML parse [message #686957 is a reply to message #686953] |
Tue, 07 February 2023 14:07 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select rownum,
x.*
from tbl,
xmltable(
xmlnamespaces(
default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"
),
'/BizData/Pyld/o:Document/o:DerivsTradRpt/o:TradData/o:Rpt/o:Tx/o:ValtnUpd/o:CtrPtySpcfcData/o:CtrPty'
passing xmldoc
columns
rptgctrpty_lei varchar2(25) path './o:RptgCtrPty/o:Id/o:LEI',
othrctrpty_lei varchar2(25) path './o:OthrCtrPty/o:Id/o:LEI',
submitagt_lei varchar2(25) path './o:SubmitgAgt/o:LEI'
) x
/
ROWNUM RPTGCTRPTY_LEI OTHRCTRPTY_LEI SUBMITAGT_LEI
---------- ------------------------- ------------------------- -------------------------
1 549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67
2 R0MUWSFPU8MPRO8K5P83 5493001GWWCBB16Z7U26 R0MUWSFPU8MPRO8K5P83
SQL>
|
|
|
|
|
|
|
|
| Re: XML parse [message #686960 is a reply to message #686959] |
Wed, 08 February 2023 04:50 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Thanks a lot for help, it works perfect.
Now I try to separete values Ccy and number.
<Amt Ccy="EUR">1122771.26</Amt>
When I add another TAG this just gives me a number, and I would like to get value from Ccy.
Amt VARCHAR2(20) PATH '//o:Valtn/o:CtrctVal/o:Amt'
<Valtn>
<CtrctVal>
<Amt Ccy="EUR">1122771.26</Amt>
<Sgn>false</Sgn>
</CtrctVal>
<TmStmp>2023-01-23T01:50:44Z</TmStmp>
<Tp>MTMA</Tp>
</Valtn>
Can you please give a a right xml parse??
thanks
[Updated on: Wed, 08 February 2023 05:10] Report message to a moderator |
|
|
|
| Re: XML parse [message #686961 is a reply to message #686960] |
Wed, 08 February 2023 07:20 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select x.*
from tbl,
xmltable(
xmlnamespaces(
default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
'urn:iso:std:iso:20022:tech:xsd:auth.030.001.01' as "o"
),
'/BizData/Pyld/o:Document/o:DerivsTradRpt/o:TradData/o:Rpt/o:Tx/o:ValtnUpd/o:CtrPtySpcfcData'
passing xmldoc
columns
rptgctrpty_lei varchar2(25) path './o:CtrPty/o:RptgCtrPty/o:Id/o:LEI',
othrctrpty_lei varchar2(25) path './o:CtrPty/o:OthrCtrPty/o:Id/o:LEI',
submitagt_lei varchar2(25) path './o:CtrPty/o:SubmitgAgt/o:LEI',
amount number path './o:Valtn/o:CtrctVal/o:Amt',
currency varchar2(3) path './o:Valtn/o:CtrctVal/o:Amt/@Ccy'
) x
/
RPTGCTRPTY_LEI OTHRCTRPTY_LEI SUBMITAGT_LEI AMOUNT CUR
------------------------- ------------------------- ------------------------- ---------- ---
549300E5ENQVY2IBLF67 529900BJGD0X650NVB68 549300E5ENQVY2IBLF67 1122771.26 EUR
R0MUWSFPU8MPRO8K5P83 5493001GWWCBB16Z7U26 R0MUWSFPU8MPRO8K5P83
SQL>
|
|
|
|
|
|
| Re: XML parse [message #686963 is a reply to message #686962] |
Wed, 08 February 2023 12:21 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Just one more question.
If my XML consist many tags (for instance 90 tags), do I need to do parsing for each tag ??
I would like to avoid to write every single sql expression (varchar2(25) path ...) .
thanks for advice
|
|
|
|
|
|
| Re: XML parse [message #686965 is a reply to message #686964] |
Wed, 08 February 2023 12:55 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
If I would like to display all tags (xml values), do I need to write every single expression ?
|
|
|
|
| Re: XML parse [message #686967 is a reply to message #686965] |
Wed, 08 February 2023 13:04 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
mape wrote on Wed, 08 February 2023 10:55
If I would like to display all tags (xml values), do I need to write every single expression ?
As far as I know you will need to create a column description including the path for every tag that has a value that you want to display. I noticed there are a lot of tags that do not have values and you do not need to do this for those. Most of them however are part of paths to values that you do want. You can use // to indicate everything from what is before to the next whatever as long as that is unique enough to distinguish which one. You also need to be careful of tablespaces. You can test a complete path, then experiment to see how you can shorten it.
Although there are SQL queries to create XML and HTML from Oracle data easily, I don't know of any simpler automated ways to do the reverse, converting XML to Oracle tables. However, there may be other experts here who know another way. Additionally, I believe there may be some utilities that you can purchase that will do this if you search the web or somebody may have posted some such thing available for free.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 04:35:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
