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 Go to next message
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!

Report message to a moderator

Re: XML-Element with same Tag [message #680503 is a reply to message #680502] Tue, 19 May 2020 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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 #680505 is a reply to message #680503] Tue, 19 May 2020 04:38 Go to previous messageGo to next message
mablaser
Messages: 3
Registered: May 2020
Location: Bern
Junior Member
Many thanks for your fast reply! In my XML is a Namespace. How does your query work with a namespace? Here the original XML:


<?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>
		<GrpHdr>
			<MsgId>ABCD123456</MsgId>
			<CreDtTm>2020-03-10T08:59:39.462+01:00</CreDtTm>
			<NbOfTxs>1</NbOfTxs>
			<TtlIntrBkSttlmAmt Ccy="CHF">100.00</TtlIntrBkSttlmAmt>
			<IntrBkSttlmDt>2020-03-10</IntrBkSttlmDt>
			<SttlmInf>
				<SttlmMtd>CLRG</SttlmMtd>
			</SttlmInf>
			<InstgAgt>
				<FinInstnId>
					<ClrSysMmbId>
						<ClrSysId>
							<Cd>CHSIC</Cd>
						</ClrSysId>
						<MmbId>090002</MmbId>
					</ClrSysMmbId>
				</FinInstnId>
			</InstgAgt>
			<InstdAgt>
				<FinInstnId>
					<ClrSysMmbId>
						<ClrSysId>
							<Cd>CHSIC</Cd>
						</ClrSysId>
						<MmbId>007005</MmbId>
					</ClrSysMmbId>
				</FinInstnId>
			</InstdAgt>
		</GrpHdr>
		<CdtTrfTxInf>
			<PmtId>
				<EndToEndId>NOTPROVIDED</EndToEndId>
				<TxId>987654XXXXX</TxId>
			</PmtId>
			<PmtTpInf>
				<SvcLvl>
					<Cd>URGP</Cd>
				</SvcLvl>
				<LclInstrm>
					<Prtry>CSTPMT</Prtry>
				</LclInstrm>
			</PmtTpInf>
			<IntrBkSttlmAmt Ccy="CHF">100.00</IntrBkSttlmAmt>
			<SttlmPrty>NORM</SttlmPrty>
			<SttlmTmIndctn>
				<CdtDtTm>2020-03-10T09:00:07</CdtDtTm>
			</SttlmTmIndctn>
			<ChrgBr>SHAR</ChrgBr>
			<Dbtr>
				<Nm>Absender</Nm>
				<PstlAdr>
					<StrtNm>Teststrasse</StrtNm>
					<BldgNb>99</BldgNb>
					<PstCd>1234</PstCd>
					<TwnNm>Irgendwo</TwnNm>
					<Ctry>CH</Ctry>
				</PstlAdr>
			</Dbtr>
			<DbtrAcct>
				<Id>
					<IBAN>CH123456789</IBAN>
				</Id>
			</DbtrAcct>
			<DbtrAgt>
				<FinInstnId>
					<ClrSysMmbId>
						<ClrSysId>
							<Cd>CHSIC</Cd>
						</ClrSysId>
						<MmbId>090002</MmbId>
					</ClrSysMmbId>
					<Nm>Absenderbank</Nm>
					<PstlAdr>
						<Ctry>CH</Ctry>
						<AdrLine>Absenderstrasse</AdrLine>
						<AdrLine>1234 Absenderort</AdrLine>
					</PstlAdr>
				</FinInstnId>
			</DbtrAgt>
			<CdtrAgt>
				<FinInstnId>
					<ClrSysMmbId>
						<ClrSysId>
							<Cd>CHSIC</Cd>
						</ClrSysId>
						<MmbId>007005</MmbId>
					</ClrSysMmbId>
				</FinInstnId>
			</CdtrAgt>
			<Cdtr>
				<Nm>Mustername</Nm>
				<PstlAdr>
					<AdrLine>Testadresse</AdrLine>
					<AdrLine>1234 Ort</AdrLine>
				</PstlAdr>
			</Cdtr>
			<CdtrAcct>
				<Id>
					<IBAN>CHxxxxx</IBAN>
				</Id>
			</CdtrAcct>
			<RmtInf>
				<Ustrd>Mitteilung</Ustrd>
			</RmtInf>
		</CdtTrfTxInf>
	</FIToFICstmrCdtTrf>
</Document>

Report message to a moderator

Re: XML-Element with same Tag [message #680507 is a reply to message #680505] Tue, 19 May 2020 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

XMLTABLE takes a first parameter XMLNAMESPACES.

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 Go to previous messageGo to next message
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

Report message to a moderator

Re: XML-Element with same Tag [message #680510 is a reply to message #680509] Tue, 19 May 2020 08:30 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
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?

Report message to a moderator

Previous Topic: Applet build error - Array index out of range
Next Topic: Does anyone have anything good to say about ADF with JDeveloper?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 07:32:18 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.