Re: extracting data from XML using sql

Jack,

Simple example using your partial xml:
https://gist.github.com/xtender/c71c5cefaaa3242daaba4ddadf286708 from t,
xmltable(
'//*:WorkflowParameterBinding[_at_ParameterName eq "WorkflowDerived_ftpHostName"]/*:WorkflowParameterBinding.Value/*:String' passing xmltype(t.xmlcol)
columns
x varchar2(100) path 'text()'
) xt;

As you can see I've used *: to avoid full path with namespace specification, and just filtered all children (//) to get the required node

On Tue, May 5, 2020 at 4:48 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Jack,
>
> Your xml doesn't look valid. Could you post original full xml on pastebin
> or gist.github.com?
>
> On Tue, May 5, 2020 at 3:24 AM Jack van Zanen <jack_at_vanzanen.com> wrote:
>
>> 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
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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