MIME-Version: 1.0
From: Jack van Zanen <jack@vanzanen.com>
Date: Tue, 5 May 2020 10:23:35 +1000
Message-ID: <CAFeFPA8qxg=CvUeK1ubfu68Jib3SH-noMfxJjsSGzhVbdhBYDA@mail.gmail.com>
Subject: extracting data from XML using sql
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="00000000000032e71b05a4dba923"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jack@vanzanen.com
--00000000000032e71b05a4dba923
Content-Type: text/plain; charset="UTF-8"

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

--00000000000032e71b05a4dba923
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi All<br></div><div><br></div><div>Any XML=C2=A0 gur=
u out there that can help me with this</div><div><br></div><div><br></div><=
div>I have a CLOB field with data like this below and I would like to extra=
ct the=C2=A0

WorkflowDerived_ftpHostName with value XXX.XXX.XXX.XXX in below example.<br=
></div><div><br></div><div>I am very novice with XML queries and the exampl=
es I found have very basic XML and when I try to modify it just gives error=
. ANy hints tips, examples=C2=A0 would be really appreciated</div><div><br>=
</div>&quot;&lt;?xml version=3D&quot;1.0&quot; encoding=3D&quot;utf-16&quot=
;?&gt;&lt;ns0:WorkflowTemplateActivity x:Class=3D&quot;Vendor.Workflow.Acti=
vities.WorkflowTemplate.WorkflowDerived&quot; ProcessFlowID=3D&quot;577&quo=
t; x:Name=3D&quot;WorkflowDerived&quot; ProcessDescription=3D&quot;Some Wor=
kflow Name&quot; LastModified=3D&quot;26/03/2020&quot; ProcessID=3D&quot;0&=
quot; InstanceBehavior=3D&quot;Multiple&quot; CreatedInVersion=3D&quot;6.04=
.11.24.01&quot; ModificationComment=3D&quot;20140303 120000&quot; Consolida=
tionField=3D&quot;{x:Null}&quot; RowID=3D&quot;AAAPOwAAWAABajeAAF&quot; Cha=
nged=3D&quot;True&quot; DataPartitioningFields=3D&quot;{x:Null}&quot; GlxTi=
meStamp=3D&quot;245893507000210&quot; LeadingObjectField=3D&quot;{x:Null}&q=
uot; Guid=3D&quot;069E1B1F-B3CE-946B-E053-7E28540AEBA6&quot; CreatedBy=3D&q=
uot;EB&quot; ID=3D&quot;577&quot; ModifiedInVersion=3D&quot;6.05.03.10.18&q=
uot; ModifiedBy=3D&quot;jmoseley&quot; Revision=3D&quot;$Revision: 210133 $=
&quot; CreatedOn=3D&quot;30/10/2014&quot; xmlns:ns1=3D&quot;clr-namespace:V=
endor.Workflow.Activities;Assembly=3DVendor.Workflow.Activities, Version=3D=
1.0.0.0, Culture=3Dneutral, PublicKeyToken=3Dnull&quot; xmlns=3D&quot;<a hr=
ef=3D"http://schemas.microsoft.com/winfx/2006/xaml/workflow">http://schemas=
.microsoft.com/winfx/2006/xaml/workflow</a>&quot; xmlns:x=3D&quot;<a href=
=3D"http://schemas.microsoft.com/winfx/2006/xaml">http://schemas.microsoft.=
com/winfx/2006/xaml</a>&quot; xmlns:ns0=3D&quot;clr-namespace:Vendor.Workfl=
ow.Activities.WorkflowTemplate;Assembly=3DVendor.Workflow.Activities, Versi=
on=3D1.0.0.0, Culture=3Dneutral, PublicKeyToken=3Dnull&quot;&gt;<br>	&lt;ns=
0:WorkflowTemplateActivity.ParameterBindings&gt;<br>		&lt;WorkflowParameter=
Binding ParameterName=3D&quot;WorkflowDerived_ftpHostName&quot;&gt;<br>			&=
lt;WorkflowParameterBinding.Value&gt;<br>				&lt;ns2:String xmlns:ns2=3D&qu=
ot;clr-namespace:System;Assembly=3Dmscorlib, Version=3D4.0.0.0, Culture=3Dn=
eutral, PublicKeyToken=3Db77a5c561934e089&quot;&gt;xxx.xxx.xxx.xxx&lt;/ns2:=
String&gt;<br>			&lt;/WorkflowParameterBinding.Value&gt;<br>		&lt;/Workflow=
ParameterBinding&gt;<div>...</div><div>...</div><div>...</div><div>...</div=
><div><br></div><div><br clear=3D"all"><div><div dir=3D"ltr" class=3D"gmail=
_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div>Jack v=
an Zanen</div><div><br></div><div><img src=3D"https://docs.google.com/uc?id=
=3D0BwovDucFT1fXaEREVHNWRWZyNjg&amp;export=3Ddownload" width=3D"96" height=
=3D"96"><br>------------------------- <br>This e-mail and any attachments m=
ay contain confidential material for the sole use of the intended recipient=
. If you are not the intended recipient, please be aware that any disclosur=
e, copying, distribution or use of this e-mail or any attachment is prohibi=
ted. If you have received this e-mail in error, please contact the sender a=
nd delete all copies.<br>Thank you for your cooperation </div></div></div><=
/div></div></div>

--00000000000032e71b05a4dba923--
--
http://www.freelists.org/webpage/oracle-l