Oracle XMLType - iterating over elements with certain attribute values

Question

I have the following xml stored in a field in an Oracle database:

Activity mc:Ignorable="sap sap2010 sads" x:Class="Process"
     xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities"
     xmlns:av="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
     xmlns:awaw="clr-namespace:A.Workflow.Activities.WSCall;assembly=A.Workflow.Activities"
     xmlns:awd="clr-namespace:A.Workflow.DataObjects;assembly=A.Workflow.DataObjects"
     xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
     xmlns:mva="clr-namespace:Microsoft.VisualBasic.Activities;assembly=System.Activities"
     xmlns:sads="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger"
     xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation"
     xmlns:sap2010="http://schemas.microsoft.com/netfx/2010/xaml/activities/presentation"
     xmlns:scg="clr-namespace:System.Collections.Generic;assembly=mscorlib"
     xmlns:sco="clr-namespace:System.Collections.ObjectModel;assembly=mscorlib"
     xmlns:sd="clr-namespace:System.Data;assembly=System.Data"
     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
          x:Members
            x:Property Name="GlobalMessagesCollectionIn" Type="InArgument(awd:GlobalMessagesCollection)" />
            x:Property Name="EventDataCollectionIn" Type="InArgument(awd:DataFileCollection)" />
            x:Property Name="DocumentDataCollectionIn" Type="InArgument    (awd:DataFileCollection)" />
            x:Property Name="ResultDataCollectionIn" Type="InArgument    (awd:DataFileCollection)" />
            x:Property Name="BrokerRefIn" Type="InArgument(x:String)" />
            x:Property Name="ClientFeeRefIn" Type="InArgument(x:String)" />
            x:Property Name="InitUserTeamRefIn" Type="InArgument(x:String)" />
            x:Property Name="InitUserTeamNameIn" Type="InArgument(x:String)" />
            x:Property Name="InitUserBrokOffRefIn" Type="InArgument(x:String)" />
          /x:Members>
          mva:VisualBasic.Settings>
            <x:Null />
          /mva:VisualBasic.Settings>

        /Activity

I am trying to write pl/sql procedure which - let's say outputs @Name attribute value for each x:Property element, where the Name attribute contains substring 'In'.

The following is my attempt at solving the problem:

declare 
p_ProcessFile xmltype;

BEGIN
 SELECT ProcessFile
      INTO p_ProcessFile
      FROM Process
     WHERE ProcessSeqNo = 4034; --This fetches the above xml into xmltype var

    FOR i IN (select p_ProcessFile.extract( '//x:Members/x:Property[(contains(@Name, "In")) ]/@Name', 'xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"').getStringVal() AS testing  FROM dual) LOOP
      dbms_output.put_line(i.testing || ';');
     END LOOP;

END;

Unfortunately, this outputs all values concatenated:

GlobalMessagesCollectionInEventDataCollectionInDocumentDataCollectionInResultDataCollectionInBrokerRefIn;

However, what I'm after is:

GlobalMessagesCollectionIn
EventDataCollectionIn
DocumentDataCollectionIn
ResultDataCollectionIn
etc

How can I iterate for each x:Property with the @Name attribute containing 'In'?

Alex Poole · Accepted Answer · 2015-07-01 16:00:02Z

3

Perhaps worth noting that XMLSEQUNCE is deprecated.

You can achieve the same result in plain SQL - with no PL/SQL block or looping required - with XMLTABLE:

select x.name
from process p
cross join xmltable(
  xmlnamespaces('http://schemas.microsoft.com/winfx/2006/xaml' as "x"),
  '//x:Property[(contains(@Name, "In"))]'
  passing p.processfile
  columns name varchar2(30) path '@Name'
) x
where p.processseqno = 4034;

NAME                         
------------------------------
GlobalMessagesCollectionIn    
EventDataCollectionIn         
DocumentDataCollectionIn      
ResultDataCollectionIn        
BrokerRefIn                   
ClientFeeRefIn                
InitUserTeamRefIn             
InitUserTeamNameIn            
InitUserBrokOffRefIn

answered Jul 1, 2015 at 16:00

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arkadiusz Łukasiewicz · Accepted Answer · 2015-07-01 09:52:26Z

2

XMLSEQUENCE. Replace yout for loop with this.

for i in (   SELECT VALUE(p).extract('//x:Property[(contains(@Name, "In"))]/@Name', 'xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"').getStringVal() val
   FROM TABLE(XMLSEQUENCE(EXTRACT(p_ProcessFile,  '//x:Members/x:Property[(contains(@Name, "In"))]', 'xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"'))) p  ) loop
  dbms_output.put_line(i.val); 
end loop;

answered Jul 1, 2015 at 9:52

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

1 Comment

Marcin Over a year ago

Thank you saviour. So, does it work because XMLSequence converted all elements into an array of 'separate' xml docs?

Collectives™ on Stack Overflow

Oracle XMLType - iterating over elements with certain attribute values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related