XMLTABLE example

output.sql

SQL> !cat tests/xml1.sql
with
  t(xmlcol) as (select '<?xml version="1.0" encoding="utf-16"?><ns0:WorkflowTemplateActivity
 x:Class="Vendor.Workflow.Activities.WorkflowTemplate.WorkflowDerived"
 ProcessFlowID="577"
x:Name="WorkflowDerived" ProcessDescription="Some Workflow Name" LastModified="26/03/2020" ProcessID="0"
InstanceBehavior="Multiple" CreatedInVersion="6.04.11.24.01"
ModificationComment="20140303 120000" ConsolidationField="{x:Null}"
RowID="AAAPOwAAWAABajeAAF" Changed="True" DataPartitioningFields="{x:Null}"
GlxTimeStamp="245893507000210" LeadingObjectField="{x:Null}"
Guid="069E1B1F-B3CE-946B-E053-7E28540AEBA6" CreatedBy="EB" ID="577"
ModifiedInVersion="6.05.03.10.18" ModifiedBy="jmoseley"
Revision="$Revision: 210133 $" CreatedOn="30/10/2014"
xmlns:ns1="clr-namespace:Vendor.Workflow.Activities;Assembly=Vendor.Workflow.Activities,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/workflow"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:ns0="clr-namespace:Vendor.Workflow.Activities.WorkflowTemplate;Assembly=Vendor.Workflow.Activities,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
<ns0:WorkflowTemplateActivity.ParameterBindings>
<WorkflowParameterBinding ParameterName="WorkflowDerived_ftpHostName">
<WorkflowParameterBinding.Value>
<ns2:String xmlns:ns2="clr-namespace:System;Assembly=mscorlib,Version=4.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089">xxx.xxx.xxx.xxx</ns2:String>
</WorkflowParameterBinding.Value>
</WorkflowParameterBinding>
</ns0:WorkflowTemplateActivity.ParameterBindings>
</ns0:WorkflowTemplateActivity>'
from dual)
select--+ no_query_transformation no_xml_query_rewrite
   xt.*
from t,
  xmltable(
     '//*:WorkflowParameterBinding[@ParameterName eq "WorkflowDerived_ftpHostName"]/*:WorkflowParameterBinding.Value/*:String'
     passing xmltype(t.xmlcol)
     columns
        x varchar2(100) path 'text()'
  ) xt;
SQL> @tests/xml1.sql

X
----------------------------------------------------------------------------------------------------
xxx.xxx.xxx.xxx

xml1.sql

with
  t(xmlcol) as (select '<?xml version="1.0" encoding="utf-16"?><ns0:WorkflowTemplateActivity
 x:Class="Vendor.Workflow.Activities.WorkflowTemplate.WorkflowDerived"
 ProcessFlowID="577"
x:Name="WorkflowDerived" ProcessDescription="Some Workflow Name" LastModified="26/03/2020" ProcessID="0"
InstanceBehavior="Multiple" CreatedInVersion="6.04.11.24.01"
ModificationComment="20140303 120000" ConsolidationField="{x:Null}"
RowID="AAAPOwAAWAABajeAAF" Changed="True" DataPartitioningFields="{x:Null}"
GlxTimeStamp="245893507000210" LeadingObjectField="{x:Null}"
Guid="069E1B1F-B3CE-946B-E053-7E28540AEBA6" CreatedBy="EB" ID="577"
ModifiedInVersion="6.05.03.10.18" ModifiedBy="jmoseley"
Revision="$Revision: 210133 $" CreatedOn="30/10/2014"
xmlns:ns1="clr-namespace:Vendor.Workflow.Activities;Assembly=Vendor.Workflow.Activities,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/workflow"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:ns0="clr-namespace:Vendor.Workflow.Activities.WorkflowTemplate;Assembly=Vendor.Workflow.Activities,Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
<ns0:WorkflowTemplateActivity.ParameterBindings>
<WorkflowParameterBinding ParameterName="WorkflowDerived_ftpHostName">
<WorkflowParameterBinding.Value>
<ns2:String xmlns:ns2="clr-namespace:System;Assembly=mscorlib,Version=4.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089">xxx.xxx.xxx.xxx</ns2:String>
</WorkflowParameterBinding.Value>
</WorkflowParameterBinding>
</ns0:WorkflowTemplateActivity.ParameterBindings>
</ns0:WorkflowTemplateActivity>'
from dual)
select--+ no_query_transformation no_xml_query_rewrite
   xt.*
from t,
  xmltable(
     '//*:WorkflowParameterBinding[@ParameterName eq "WorkflowDerived_ftpHostName"]/*:WorkflowParameterBinding.Value/*:String'
     passing xmltype(t.xmlcol)
     columns
        x varchar2(100) path 'text()'
  ) xt;