Home » Developer & Programmer » JDeveloper, Java & XML » XML-Element with same Tag (Oracle 12.2.0.1.0)
XML-Element with same Tag [message #680502] |
Tue, 19 May 2020 02:43 |
|
mablaser
Messages: 3 Registered: May 2020 Location: Bern
|
Junior Member |
|
|
Hi all
I have a field with XML data in a database. A tag occurs several times in XML. How can I read this out so that each element is displayed in a column? With extractvalue i don't get any further because the element occurs several times.
XML:
<Cdtr>
<Nm>Mustername</Nm>
<PstlAdr>
<AdrLine>Testadresse</AdrLine>
<AdrLine>1234 Ort</AdrLine>
</PstlAdr>
</Cdtr>
Desired result:
Nm | AdrLine1 | AdrLine 2
Mustername | Testadresse |1234 Ort
Thanks for your reply!
|
|
|
Re: XML-Element with same Tag [message #680503 is a reply to message #680502] |
Tue, 19 May 2020 03:37 |
|
Michel Cadot
Messages: 68699 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
SQL> with
2 data as (
3 select '<Cdtr>
4 <Nm>Mustername</Nm>
5 <PstlAdr>
6 <AdrLine>Testadresse</AdrLine>
7 <AdrLine>1234 Ort</AdrLine>
8 </PstlAdr>
9 </Cdtr>' data from dual
10 )
11 select x.nm,
12 max(decode(y.pos, 1, y.addr)) Addr1,
13 max(decode(y.pos, 2, y.addr)) Addr2
14 from data,
15 xmltable('/Cdtr' passing xmltype(data)
16 columns
17 nm varchar2(20) path '/Cdtr/Nm',
18 addrs xmltype path '/Cdtr/PstlAdr/AdrLine') x,
19 xmltable('/AdrLine' passing x.addrs
20 columns
21 pos for ordinality,
22 addr varchar2(20) path '/AdrLine') y
23 group by x.nm
24 /
NM ADDR1 ADDR2
-------------------- -------------------- --------------------
Mustername Testadresse 1234 Ort
[Updated on: Tue, 19 May 2020 03:38] Report message to a moderator
|
|
|
|
|
Re: XML-Element with same Tag [message #680509 is a reply to message #680507] |
Tue, 19 May 2020 07:17 |
|
mablaser
Messages: 3 Registered: May 2020 Location: Bern
|
Junior Member |
|
|
sorry, i am not able to define the namespace correctly. How exactly does that have to be done?
with
data as (
select '<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="http://www.six-interbank-clearing.com/de/pacs.008.001.02.ch.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FIToFICstmrCdtTrf>
<CdtTrfTxInf>
<Cdtr>
<Nm>Mustername</Nm>
<PstlAdr>
<AdrLine>Testadresse</AdrLine>
<AdrLine>1234 Ort</AdrLine>
</PstlAdr>
</Cdtr>
</CdtTrfTxInf>
</FIToFICstmrCdtTrf>
</Document>' data
from dual
)
select x.nm,
max(decode(y.pos, 1, y.addr)) Addr1,
max(decode(y.pos, 2, y.addr)) Addr2
from data,
xmltable(xmlnamespaces(default 'http://www.six-interbank-clearing.com/de/pacs.008.001.02.ch.02', 'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
,'/Document' passing xmltype(data)
columns
nm varchar2(20) path '/Document/FIToFICstmrCdtTrf/CdtTrfTxInf/Cdtr/Nm',
addrs xmltype path '/Document/FIToFICstmrCdtTrf/CdtTrfTxInf/Cdtr/PstlAdr/AdrLine'
) x,
xmltable('/AdrLine' passing x.addrs
columns
pos for ordinality,
addr varchar2(20) path '/AdrLine') y
group by x.nm
|
|
|
Re: XML-Element with same Tag [message #680510 is a reply to message #680509] |
Tue, 19 May 2020 08:30 |
|
Michel Cadot
Messages: 68699 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Well, I don't know, I admit I have never use XMLTABLE with namespaces.
This works:
SQL> with
2 data as (
3 select '<?xml version="1.0" encoding="UTF-8"?>
4 <Document>
5 <FIToFICstmrCdtTrf>
6 <CdtTrfTxInf>
7 <Cdtr>
8 <Nm>Mustername</Nm>
9 <PstlAdr>
10 <AdrLine>Testadresse</AdrLine>
11 <AdrLine>1234 Ort</AdrLine>
12 </PstlAdr>
13 </Cdtr>
14 </CdtTrfTxInf>
15 </FIToFICstmrCdtTrf>
16 </Document>' data
17 from dual
18 )
19 select x.nm,
20 max(decode(y.pos, 1, y.addr)) Addr1,
21 max(decode(y.pos, 2, y.addr)) Addr2
22 from data,
23 xmltable('/Document/FIToFICstmrCdtTrf/CdtTrfTxInf/Cdtr' passing xmltype(data)
24 columns
25 nm varchar2(20) path '/Cdtr/Nm',
26 addrs xmltype path '/Cdtr/PstlAdr/AdrLine'
27 ) x,
28 xmltable('/AdrLine' passing x.addrs
29 columns
30 pos for ordinality,
31 addr varchar2(20) path '/AdrLine') y
32 group by x.nm
33 /
NM ADDR1 ADDR2
-------------------- -------------------- --------------------
Mustername Testadresse 1234 Ort
But this doesn't:
SQL> with
2 data as (
3 select '<?xml version="1.0" encoding="UTF-8"?>
4 <Document xmlns="http://www.six-interbank-clearing.com/de/pacs.008.001.02.ch.02"
5 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
6 <FIToFICstmrCdtTrf>
7 <CdtTrfTxInf>
8 <Cdtr>
9 <Nm>Mustername</Nm>
10 <PstlAdr>
11 <AdrLine>Testadresse</AdrLine>
12 <AdrLine>1234 Ort</AdrLine>
13 </PstlAdr>
14 </Cdtr>
15 </CdtTrfTxInf>
16 </FIToFICstmrCdtTrf>
17 </Document>' data
18 from dual
19 )
20 select x.nm,
21 max(decode(y.pos, 1, y.addr)) Addr1,
22 max(decode(y.pos, 2, y.addr)) Addr2
23 from data,
24 xmltable(xmlnamespaces(default 'http://www.six-interbank-clearing.com/de/pacs.008.001.02.ch.02',
25 'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
26 ),
27 '/Document/FIToFICstmrCdtTrf/CdtTrfTxInf/Cdtr' passing xmltype(data)
28 columns
29 nm varchar2(20) path '/Cdtr/Nm',
30 addrs xmltype path '/Cdtr/PstlAdr/AdrLine'
31 ) x,
32 xmltable('/AdrLine' passing x.addrs
33 columns
34 pos for ordinality,
35 addr varchar2(20) path '/AdrLine') y
36 group by x.nm
37 /
no rows selected
Maybe because I have no access to namespace?
|
|
|
Goto Forum:
Current Time: Tue Oct 08 22:57:05 CDT 2024
|