Re: Find string in multiple it's files using sql services -- MIDRANGE-L

Hi again

I looked at this again, tried it at work today - it doesn't quite fit my needs, because path_name does not contain any identifying text - all the names of the IFS files are GUIDs.

What I need is to get a list of XMLs that have certain values inside them.

Lots of good stuff to learn, though! Thanks again!

Vern

On 8/16/2022 4:01 AM, Niels Liisberg wrote:

Good to hear Vern;

So you can basically boil it down to something like :

with xmldata as (
select path_name , xml2clob( xmlparse (document get_xml_file(path_name))) payload
from table (
qsys2.ifs_object_statistics(
start_path_name => '/tmp/',
subtree_directories => 'NO',
object_type_list => '*STMF'
)
)
where lower(path_name) like '%cust%.xml'
)
select path_name ,payload
from xmldata
where payload like '%ID%'
with ur;

On Tue, Aug 16, 2022 at 9:57 AM Vern Hamberg via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi Niels

All this is well and good but not needed for what I am doing. We
already
use XMLTABLE in out programs to consume the information and put
into our
application tables.

One of the main things I am interested in is to get a list of the
XMLs
that contain certain kind of information - it could be about a
service
done in the field, a status message from the field, or something
else.
The XML has a top-level element like <Service> to identify these. The
name of the XMLs are GUIDs - not a clue about __what__ the content is
about. (In actual use, we get a list of GUIDs for each type, I
believe -
I am not the person doing most of this development - so we know what
they are the first time we see them. But once they have arrived, I
don't
know what type they are from the name.

So I am interested in a list of file names that are service
information.
I am also interested to pull a certain value within the XML. I can
see
that XMLTABLE could be useful there, but I'm not interested here in a
full shredding of the content into tables.

So using locate and substring works very well, both for the column
list
and for the WHERE clause.

Again, this is a kind of ad hoc thing - I have a custom "example"
in RSS
that I can use, setting values of a couple global variables, and
off I go!!

I do like what you present, it is just more than I need, for now,
anyhow!

Vern

On 8/16/2022 2:33 AM, Niels Liisberg wrote:
> Here is my take on that:
>
> You can pasthe the following directly into ACS - SQL script and
play with
> it:
>
> ___________________________
>
> -- Lets say your XML is a representation of a simple table with
rows like
> this one:Examples
> Select *
> from qiws.qcustcdt;
>
> -- Now lets save that table ( id and name) as a XML file to the
IFS so we
> have something to play with:
> call ifs_write_utf8 (
> path_name =>'/tmp/mycust.xml',
> line => xml2clob((
> Select
> xmlelement( name "CUSTOMERS" ,
> xmlagg(
> xmlelement( name "CUSTOMER" ,
> xmlelement( name "ID" , cusnum),
> xmlelement( name "NAME" , trim(LSTNAM))
> )
> )
> )
> from qiws.qcustcdt
> )) ,
> overwrite => 'REPLACE',
> end_of_line => 'NONE'
> );
>
> -- Now to your real question:
> -- First it you need to look into the XML ( any XML) -you can
use this
> approach:
> -- note the "with ur" that is "uncommitted read" - you need
commit since
> XML is streams
> values (
> xmlparse (document get_xml_file('/tmp/mycust.xml'))
> ) with ur;
>
>
> -- You could be tempted to simply use POS or INSTR in the above
to find a
> specific value.
> -- However since SQL supports the access to the XML as table we
can use
> this much more robust way to do it:
> Select *
> from xmltable(
> '/CUSTOMERS/CUSTOMER'
> passing xmlparse (document get_xml_file('/tmp/mycust.xml'))
> columns
> id bigint path 'ID',
> name varchar(32) path 'NAME'
> )
> with ur;
>
> -- So now we have the XML data as tabular info. But you need to
> -- to read it from a directory of XML files.
> -- Here you can combine the CTE list of XML files
> -- and join/pass each file
> with XML_files as (
> select path_name
> from table (
> qsys2.ifs_object_statistics(
> start_path_name => '/tmp/',
> subtree_directories => 'NO',
> object_type_list => '*STMF'
> )
> )
> where lower(path_name) like '%cust%.xml'
> ),
> customer_from_XML_files as (
> select *
> from XML_files , xmltable(
> '/CUSTOMERS/CUSTOMER'
> passing xmlparse (
> document get_xml_file(path_name)
> )
> columns
> id bigint path 'ID',
> name varchar(32) path 'NAME'
> )
> )
> select *
> from customer_from_XML_files
> with ur;
>
> -- Voila !! XML files as rows you do all the SQL magic on you
can imagine
>
>
>
>
>
>
> On Sun, Aug 14, 2022 at 10:59 AM Birgitta Hauser
<Hauser@xxxxxxxxxxxxxxx>
> wrote:
>
>> Vern,
>>
>> You had to use the CROSS JOIN and LATERAL because you used the
IFS_READ in
>> a way that it returned multiple rows.
>> I used Get_Clob_From_File(Path_Name) which returns a
LOB-Locator on the
>> IFS file. LOB locators can be used like character columns. So I
used it in
>> the WHERE Condition in composition with the LIKE predicate.
>> If you only want to know in which file the data is located, using
>> GET_CLOB_FROM_FILE is the easiest way (no need of any CROSS JOIN or
>> LATERAL).
>> If you exactly want to see the row and the content of the row,
where you
>> find the string, your solution is the better one.
>>
>> Mit freundlichen Grüßen / Best regards
>>
>> Birgitta Hauser
>> Modernization – Education – Consulting on IBM i
>>
>>
>> "Shoot for the moon, even if you miss, you'll land among the
stars." (Les
>> Brown)
>> "If you think education is expensive, try ignorance." (Derek Bok)
>> "What is worse than training your staff and losing them? Not
training them
>> and keeping them!"
>> „Train people well enough so they can leave, treat them well
enough so
>> they don't want to.“ (Richard Branson)
>>
>>
>> -----Original Message-----
>> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On
Behalf Of
>> Vern Hamberg via MIDRANGE-L
>> Sent: Samstag, 13. August 2022 18:17
>> To: midrange-l@xxxxxxxxxxxxxxxxxx
>> Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
>> Subject: Re: Find string in multiple it's files using sql services
>>
>> Thanks, Birgitta! I finally got a solution, similar to yours,
might be the
>> first real use I've made of LATERAL.
>>
>> The problem was, I got a message from Ifs_Read that said
>> '/YourFolder/Dir2/Dir3' was not valid for the operation - well,
obviously,
>> because it is a directory!! I did not have the named parameter
>> Object_Type_List - now I do. Here's what I came up with using
CROSS JOIN
>> LATERAL with Ifs_Read - and a couple global variables.
>>
>> This doesn't need to be run under commitment control, although
that isn't
>> usually a problem, as I recall. Performance, in any case, is
probably very
>> dependent on how many streamed files there are and their size.
>>
>> -- category: Custom
>> -- description: Find IFS files for 2 specific values create or
replace
>> variable typeofxml varchar(50); set typeofxml = '<Service>';
create or
>> replace variable testvalue varchar(32); set testvalue = 'DGDO5778';
>>
>> SELECT path_name,
>> line_number,
>> line
>> FROM TABLE (
>> qsys2.ifs_object_statistics(start_path_name =>
>> '/Lvl1/Lvl2/Guids', OBJECT_TYPE_LIST => '*STMF')
>> ) a
>> CROSS JOIN
>> LATERAL (
>> SELECT line_number,
>> line
>> FROM TABLE (
>> qsys2.ifs_read(
>> path_name => a.path_name,
end_of_line =>
>> 'ANY', maximum_line_length => default,
>> ignore_errors => 'NO')
>> )
>> ) b
>> WHERE LOCATE(UPPER(testvalue), UPPER(line)) > 0 and
>> LOCATE(UPPER(typeofxml), UPPER(line)) > 0;
>>
>> I do so appreciate the efforts of the database team at IBM for
giving us
>> these really useful tools, these SQL services. Kudos, Scott!!
and all!
>>
>> Cheers
>> Vern
>>
>> On 8/13/2022 4:09 AM, Birgitta Hauser wrote:
>>> Try something like this:
>>> Select Path_Name, Get_Clob_From_File(Path_Name) "IFS File Content"
>>> From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name =>
>> '/YourFolder/Dir2/Dir3',
>>> Subtree_Directories => 'YES',
>>> Object_Type_List =>
>> '*ALLSTMF')) x
>>> Where Right(Trim(Path_Name), 5) = '.xml'
>>> and Get_Clob_From_File(Path_Name) like '%Whatever
you are
>> looking for%'
>>> Order By Path_Name ;
>>>
>>> Note: GET_CLOB_FROM_FILE has to be run under Commitment Control
>>>
>>> Mit freundlichen Grüßen / Best regards
>>>
>>> Birgitta Hauser
>>> Modernization – Education – Consulting on IBM i
>>>
>>>
>>> "Shoot for the moon, even if you miss, you'll land among the
stars."
>>> (Les Brown) "If you think education is expensive, try ignorance."
>>> (Derek Bok) "What is worse than training your staff and losing
them? Not
>> training them and keeping them!"
>>> „Train people well enough so they can leave, treat them well
enough so
>>> they don't want to.“ (Richard Branson)
>>>
>>>
>>> -----Original Message-----
>>> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On
Behalf Of
>>> Vern Hamberg via MIDRANGE-L
>>> Sent: Samstag, 13. August 2022 00:55
>>> To: Midrange Systems Technical Discussion
>>> <midrange-l@xxxxxxxxxxxxxxxxxx>
>>> Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
>>> Subject: Re: Find string in multiple it's files using sql services
>>>
>>>
>>> I know how to use grep and all, this is not another chapter in
that
>> unpublished rag, for my purposes an sql solution serves me more
directly, I
>> wanted to avoid a command line solution.
>>> Cheers
>>> Vern
>>>
>>>
>>> -----Original Message-----
>>>
>>> From: Jack <midrange-l@xxxxxxxxxxxxxxxxxx>
>>> To: Midrange <midrange-l@xxxxxxxxxxxxxxxxxx>
>>> Cc: Jack <jwoehr@xxxxxxxxxxxxxxxxxxxxxxxx>
>>> Date: Friday, 12 August 2022 4:10 PM CDT
>>> Subject: Re: Find string in multiple it's files using sql services
>>>
>>> On Fri, Aug 12, 2022 at 10:38 AM Roger Harman
>>> <roger.harman@xxxxxxxxxxx>
>>> wrote:
>>>
>>>> As opposed to using "grep"?
>>>>
>>> The only sensible way to do it, and if you are on IBM i and not
>>> planning to off-platform, it's half-past time to learn grep sed tr
>>> etc. and your basic pipeline tools.
>>>
>>> *Another chapter in the continuing saga of "Open Source Is Not An
>>> Option, It's A Necessity".*
>>>
>>>
>>>> -----Original Message-----
>>>> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On
Behalf Of
>>>> Vern Hamberg via MIDRANGE-L
>>>> Sent: Friday, August 12, 2022 8:15 AM
>>>> To: Midrange Systems Technical Discussion
>>>> <midrange-l@xxxxxxxxxxxxxxxxxx>
>>>> Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
>>>> Subject: Find string in multiple it's files using sql services
>>>>
>>>>
>>>> I have XML files in a folder, I want to see which ones have a
certain
>>>> value in them, such as an account code. I tried
ifs_object_statistics
>>>> with start_path_name ==> set, joined to ifs_read, the
path_name from
>>>> ifs_object_statistics in path_name ==>.
>>>>
>> --
>> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
>> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe,
>> unsubscribe, or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
>> Before posting, please take a moment to review the archives at
>> https://archive.midrange.com/midrange-l.
>>
>> Please contact support@xxxxxxxxxxxxxxxxxxxx for any
subscription related
>> questions.
>>
>> Help support midrange.com <http://midrange.com> by shopping at
amazon.com <http://amazon.com> with our affiliate
>> link: https://amazon.midrange.com
>>
>> --
>> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list
>> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
>> To subscribe, unsubscribe, or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
>> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
>> Before posting, please take a moment to review the archives
>> at https://archive.midrange.com/midrange-l.
>>
>> Please contact support@xxxxxxxxxxxxxxxxxxxx for any
subscription related
>> questions.
>>
>> Help support midrange.com <http://midrange.com> by shopping at
amazon.com <http://amazon.com> with our affiliate
>> link: https://amazon.midrange.com
>>

-- This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com <http://midrange.com> by shopping at
amazon.com <http://amazon.com> with our affiliate link:
https://amazon.midrange.com