Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
select first value from XML multiple tags with same name

Hi
I'm attempting to select the first value of the alternateId tag where the attribute domain == "SEDOL".
The overall requirement is, in pseudo code:
I'll be using libxml, but for testing I need to get this working in Oracle's SQL.
sedol1 = ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"][1]sedol2 = ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"][2]alternate_sedol = ns0:trade/ns0:alternateId[@domain="AlternateSedol"]if (sedol1 is not None and sedol2 is not None) or (sedol1 is None): sedol = AlternateSedolelse: sedol = sedol1
A solution was posted in stack overflow, which shows how one can select the first element where more than one tag occurs with the same name.
I'm attempting the same XPATH method in SQL on 11gR2. The script below shows three sample XML text, the first of which follows a happy path, i.e. only a single SEDOL exists.
The majority of XML's (95%) follow such a happy path.
The rest follow one of two scenarios:
1. The second XML text (l_xml2) is an example where no SEDOL exists, in which case the AlternateSedol should be selected.
2. The third XML text is an example where more than one SEDOL occurs, and thus the alternative SEDOL should be output
At the moment, I'm getting the following exception
SQL> @get_sedol.sqlXML with a single SEDOL-----------------------Ticket : 95867856Sedol : ABCLocation : 32132XML with no SEDOL-----------------------Ticket : 34594987458Sedol : XYZ.2Location : 5656666declare*ERROR at line 1:ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence- got multi-item sequenceORA-06512: at line 155
set serveroutput ondeclarel_xml1 xmltype := xmltype('<?xml version="1.0" encoding="UTF-8"?><ns0:tradeEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="urn:lingua:trade" xmlns:ns1="urn:lingua:product:shared" xmlns:ns2="urn:lingua:base" xmlns:ns3="urn:lingua:assetflow:shared"> <ns0:id domain="GREMEA" version="0">4566564632</ns0:id> <ns0:transactionType>New</ns0:transactionType> <ns0:transactionTime>2018-11-17T12:23:01Z</ns0:transactionTime> <ns0:trade> <ns0:alternateId domain="TicketNumber">95867856</ns0:alternateId> <ns0:alternateId domain="AlternateSedol">ABC.1</ns0:alternateId> <ns1:underlyer> <ns1:basket id="basket"> <ns1:basketConstituent> <ns1:underlyingAsset> <ns1:instrumentId domain="SEDOL">ABC</ns1:instrumentId> </ns1:underlyingAsset> </ns1:basketConstituent> </ns1:basket> </ns1:underlyer> <ns3:side id="csi"> <ns3:seller id="cparty"> <ns3:partyId domain="location">32132</ns3:partyId> </ns3:seller> </ns3:side> <ns3:side id="osi"> <ns3:clearingFirm> <ns3:partyId domain="location">EMEA</ns3:partyId> </ns3:clearingFirm> </ns3:side> </ns0:trade></ns0:tradeEvent>');l_xml2 xmltype := xmltype('<?xml version="1.0" encoding="UTF-8"?><ns0:tradeEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="urn:lingua:trade" xmlns:ns1="urn:lingua:product:shared" xmlns:ns2="urn:lingua:base" xmlns:ns3="urn:lingua:assetflow:shared"> <ns0:id domain="GRAPAC" version="0">9837698657987</ns0:id> <ns0:transactionType>New</ns0:transactionType> <ns0:transactionTime>2018-11-21T14:04:19Z</ns0:transactionTime> <ns0:trade> <ns0:alternateId domain="TicketNumber">34594987458</ns0:alternateId> <ns0:alternateId domain="AlternateSedol">XYZ.2</ns0:alternateId> <ns3:side id="csi"> <ns3:seller id="cparty"> <ns3:partyId domain="location">5656666</ns3:partyId> </ns3:seller> </ns3:side> <ns3:side id="osi"> <ns3:clearingFirm> <ns3:partyId domain="location">APAC</ns3:partyId> </ns3:clearingFirm> </ns3:side> </ns0:trade></ns0:tradeEvent>');l_xml3 xmltype := xmltype('<?xml version="1.0" encoding="UTF-8"?><ns0:tradeEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="urn:lingua:trade" xmlns:ns1="urn:lingua:product:shared" xmlns:ns2="urn:lingua:base" xmlns:ns3="urn:lingua:assetflow:shared"> <ns0:id domain="GRAPAC" version="0">9837698657987</ns0:id> <ns0:transactionType>New</ns0:transactionType> <ns0:transactionTime>2018-10-17T09:23:24Z</ns0:transactionTime> <ns0:trade> <ns0:alternateId domain="TicketNumber">2365965395</ns0:alternateId> <ns0:alternateId domain="AlternateSedol">XYZ.1</ns0:alternateId> <ns3:side id="csi"> <ns3:seller id="cparty"> <ns3:partyId domain="location">97865726</ns3:partyId> </ns3:seller> </ns3:side> <ns3:side id="osi"> <ns3:clearingFirm> <ns3:partyId domain="location">INHOUSE</ns3:partyId> </ns3:clearingFirm> </ns3:side> <ns1:underlyer> <ns1:basket id="basket"> <ns1:basketConstituent> <ns1:underlyingAsset> <ns1:instrumentId domain="SEDOL">XYZ</ns1:instrumentId> </ns1:underlyingAsset> </ns1:basketConstituent> <ns1:basketConstituent> <ns1:underlyingAsset> <ns1:instrumentId domain="SEDOL">ABC</ns1:instrumentId> </ns1:underlyingAsset> </ns1:basketConstituent> </ns1:basket> </ns1:underlyer> </ns0:trade></ns0:tradeEvent>'); l_location varchar2(200); l_ticket varchar2(200); l_sedol varchar2(30);begin select x.location ,x.ticket, nvl(x.sedol,x.alternatesedol) into l_location, l_ticket,l_sedol from (select l_xml1 msg from dual ) m ,xmltable(xmlnamespaces('urn:lingua:product:shared' as "ns1" ,'urn:lingua:assetflow:shared' as "ns3" ,'urn:lingua:basefinance' as "ns4" ,'urn:lingua:trade' as "ns0" ,'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ) ,'/ns0:tradeEvent' passing m.msg columns ticket varchar2(200) path 'ns0:trade/ns0:alternateId[@domain = "TicketNumber"]' ,location varchar2(200) path 'ns0:trade/ns3:side[@id="csi"]/ns3:seller[@id="cparty"]/ns3:partyId[@domain="location"]' ,sedol varchar2(30) path 'ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"]' ,alternatesedol varchar2(30) path 'ns0:trade/ns1:alternateId[@domain="AlternateSedol"]' ) x; dbms_output.put_line(chr(13) || chr(10) || 'XML with a single SEDOL'); dbms_output.put_line('-----------------------'); dbms_output.put_line('Ticket : ' || l_ticket); dbms_output.put_line('Sedol : ' || l_sedol); dbms_output.put_line('Location : ' || l_location); select x.location ,x.ticket,x.alternatesedol into l_location, l_ticket,l_sedol from (select l_xml2 msg from dual ) m ,xmltable(xmlnamespaces('urn:lingua:product:shared' as "ns1" ,'urn:lingua:assetflow:shared' as "ns3" ,'urn:lingua:basefinance' as "ns4" ,'urn:lingua:trade' as "ns0" ,'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ) ,'/ns0:tradeEvent' passing m.msg columns ticket varchar2(200) path 'ns0:trade/ns0:alternateId[@domain = "TicketNumber"]' ,location varchar2(200) path 'ns0:trade/ns3:side[@id="csi"]/ns3:seller[@id="cparty"]/ns3:partyId[@domain="location"]' ,sedol varchar2(30) path 'ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"]' ,alternatesedol varchar2(30) path 'ns0:trade/ns0:alternateId[@domain="AlternateSedol"]' ) x; dbms_output.put_line(chr(13) || chr(10) || 'XML with no SEDOL'); dbms_output.put_line('-----------------------'); dbms_output.put_line('Ticket : ' || l_ticket); dbms_output.put_line('Sedol : ' || l_sedol); dbms_output.put_line('Location : ' || l_location); select x.location ,x.ticket, x.sedol into l_location, l_ticket,l_sedol from (select l_xml3 msg from dual ) m ,xmltable(xmlnamespaces('urn:lingua:product:shared' as "ns1" ,'urn:lingua:assetflow:shared' as "ns3" ,'urn:lingua:basefinance' as "ns4" ,'urn:lingua:trade' as "ns0" ,'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ) ,'/ns0:tradeEvent' passing m.msg columns ticket varchar2(200) path 'ns0:trade/ns0:alternateId[@domain = "TicketNumber"]' ,location varchar2(200) path 'ns0:trade/ns3:side[@id="csi"]/ns3:seller[@id="cparty"]/ns3:partyId[@domain="location"]' ,sedol varchar2(30) path 'ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"][1]' ,alternatesedol varchar2(30) path 'ns0:trade/ns0:alternateId[@domain="AlternateSedol"]' ) x; dbms_output.put_line(chr(13) || chr(10) || 'XML with multiple SEDOL''s'); dbms_output.put_line('-----------------------'); dbms_output.put_line('Ticket : ' || l_ticket); dbms_output.put_line('Sedol : ' || l_sedol); dbms_output.put_line('Location : ' || l_location);end;/
Best Answer
-
You could use [1] at the repeating tag <ns1:basketConstituent>, or use a separate, additional XMLTable.
Btw. there is a special XML DB section...
WITH m AS (
SELECT
xmltype('<?xml version="1.0" encoding="UTF-8"?>
<ns0:tradeEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ns0="urn:lingua:trade"
xmlns:ns1="urn:lingua:product:shared"
xmlns:ns2="urn:lingua:base"
xmlns:ns3="urn:lingua:assetflow:shared">
<ns0:id domain="GRAPAC" version="0">9837698657987</ns0:id>
<ns0:transactionType>New</ns0:transactionType>
<ns0:transactionTime>2018-10-17T09:23:24Z</ns0:transactionTime>
<ns0:trade>
<ns0:alternateId domain="TicketNumber">2365965395</ns0:alternateId>
<ns0:alternateId domain="AlternateSedol">XYZ.1</ns0:alternateId>
<ns3:side id="csi">
<ns3:seller id="cparty">
<ns3:partyId domain="location">97865726</ns3:partyId>
</ns3:seller>
</ns3:side>
<ns3:side id="osi">
<ns3:clearingFirm>
<ns3:partyId domain="location">INHOUSE</ns3:partyId>
</ns3:clearingFirm>
</ns3:side>
<ns1:underlyer>
<ns1:basket id="basket">
<ns1:basketConstituent>
<ns1:underlyingAsset>
<ns1:instrumentId domain="SEDOL">XYZ</ns1:instrumentId>
</ns1:underlyingAsset>
</ns1:basketConstituent>
<ns1:basketConstituent>
<ns1:underlyingAsset>
<ns1:instrumentId domain="SEDOL">ABC</ns1:instrumentId>
</ns1:underlyingAsset>
</ns1:basketConstituent>
</ns1:basket>
</ns1:underlyer>
</ns0:trade>
</ns0:tradeEvent>') msg FROM dual)
select x.location ,x.ticket, x.sedol
from m
,xmltable(xmlnamespaces('urn:lingua:product:shared' as "ns1"
,'urn:lingua:assetflow:shared' as "ns3"
,'urn:lingua:basefinance' as "ns4"
,'urn:lingua:trade' as "ns0"
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
)
,'/ns0:tradeEvent/ns0:trade' passing m.msg
columns
ticket varchar2(200) path '/ns0:trade/ns0:alternateId[@domain = "TicketNumber"]'
,location varchar2(200) path '/ns0:trade/ns3:side[@id="csi"]/ns3:seller[@id="cparty"]/ns3:partyId[@domain="location"]'
,sedol varchar2(30) path '/ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent[1]/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"]'
,alternatesedol varchar2(30) path '/ns0:trade/ns0:alternateId[@domain="AlternateSedol"]'
) x;
LOCATION TICKET SEDOL
-----------------------------------
97865726 2365965395 XYZ
Answers
-
You could use [1] at the repeating tag <ns1:basketConstituent>, or use a separate, additional XMLTable.
Btw. there is a special XML DB section...
WITH m AS (
SELECT
xmltype('<?xml version="1.0" encoding="UTF-8"?>
<ns0:tradeEvent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ns0="urn:lingua:trade"
xmlns:ns1="urn:lingua:product:shared"
xmlns:ns2="urn:lingua:base"
xmlns:ns3="urn:lingua:assetflow:shared">
<ns0:id domain="GRAPAC" version="0">9837698657987</ns0:id>
<ns0:transactionType>New</ns0:transactionType>
<ns0:transactionTime>2018-10-17T09:23:24Z</ns0:transactionTime>
<ns0:trade>
<ns0:alternateId domain="TicketNumber">2365965395</ns0:alternateId>
<ns0:alternateId domain="AlternateSedol">XYZ.1</ns0:alternateId>
<ns3:side id="csi">
<ns3:seller id="cparty">
<ns3:partyId domain="location">97865726</ns3:partyId>
</ns3:seller>
</ns3:side>
<ns3:side id="osi">
<ns3:clearingFirm>
<ns3:partyId domain="location">INHOUSE</ns3:partyId>
</ns3:clearingFirm>
</ns3:side>
<ns1:underlyer>
<ns1:basket id="basket">
<ns1:basketConstituent>
<ns1:underlyingAsset>
<ns1:instrumentId domain="SEDOL">XYZ</ns1:instrumentId>
</ns1:underlyingAsset>
</ns1:basketConstituent>
<ns1:basketConstituent>
<ns1:underlyingAsset>
<ns1:instrumentId domain="SEDOL">ABC</ns1:instrumentId>
</ns1:underlyingAsset>
</ns1:basketConstituent>
</ns1:basket>
</ns1:underlyer>
</ns0:trade>
</ns0:tradeEvent>') msg FROM dual)
select x.location ,x.ticket, x.sedol
from m
,xmltable(xmlnamespaces('urn:lingua:product:shared' as "ns1"
,'urn:lingua:assetflow:shared' as "ns3"
,'urn:lingua:basefinance' as "ns4"
,'urn:lingua:trade' as "ns0"
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
)
,'/ns0:tradeEvent/ns0:trade' passing m.msg
columns
ticket varchar2(200) path '/ns0:trade/ns0:alternateId[@domain = "TicketNumber"]'
,location varchar2(200) path '/ns0:trade/ns3:side[@id="csi"]/ns3:seller[@id="cparty"]/ns3:partyId[@domain="location"]'
,sedol varchar2(30) path '/ns0:trade/ns1:underlyer/ns1:basket[@id="basket"]/ns1:basketConstituent[1]/ns1:underlyingAsset/ns1:instrumentId[@domain="SEDOL"]'
,alternatesedol varchar2(30) path '/ns0:trade/ns0:alternateId[@domain="AlternateSedol"]'
) x;
LOCATION TICKET SEDOL
-----------------------------------
97865726 2365965395 XYZ