select first value from XML multiple tags with same name

1932140

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;/

_jum

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

_jum

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