extracting data from XML using sql

  • From: Jack van Zanen <jack@xxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 5 May 2020 10:23:35 +1000

Hi All

Any XML  guru out there that can help me with this


I have a CLOB field with data like this below and I would like to extract
the  WorkflowDerived_ftpHostName with value XXX.XXX.XXX.XXX in below
example.

I am very novice with XML queries and the examples I found have very basic
XML and when I try to modify it just gives error. ANy hints tips, examples
would be really appreciated

"<?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>
...
...
...
...


Jack van Zanen


-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

Other related posts: