Re: extracting data from XML using sql

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Fri, 8 May 2020 14:48:40 +1000
Message-ID: <CAFeFPA-nXmapfDJt3P-W5EJg=nYhkiYv8eOPO7kjA7RdZ3UfwA_at_mail.gmail.com>

Thanks, I'll check it out.
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

On Tue, May 5, 2020 at 12:00 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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

Received on Fri May 08 2020 - 06:48:40 CEST