I'm attempting to select the first value of the alternateId tag where the attribute domain == "SEDOL".
I'll be using libxml, but for testing I need to get this working in Oracle's SQL.
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.
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
set serveroutput on
declare
l_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;
/