Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

select first value from XML multiple tags with same name

1932140Nov 27 2018 — edited Nov 27 2018

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 = AlternateSedol
else:
    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.sql
XML with a single SEDOL
-----------------------
Ticket   : 95867856
Sedol    : ABC
Location : 32132
XML with no SEDOL
-----------------------
Ticket   : 34594987458
Sedol    : XYZ.2
Location : 5656666
declare
*
ERROR at line 1:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence
ORA-06512: at line 155

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;
/
This post has been answered by _jum on Nov 27 2018
Jump to Answer

Comments

_jum
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

Marked as Answer by 1932140 · Sep 27 2020
1 - 1

Post Details

Added on Nov 27 2018
#xml
1 comment
2,032 views