× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks very much Birgitta, since I never handled any xml using SQL it's all
new to me but I'll try as soon as possible.

Best regards

--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

2017-12-12 7:33 GMT+01:00 Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>:

If you want to do it with SQL, you need 2 Steps:

1. Use the XMLTABLE Table Function for converting the XML into a relational
form with all information you need. (You can add a where condition for
filtering).
2. After you converted the data into a relational form, you can use the XML
publishing functions for converting the relational data into an XML
document
again

In the the following example your XMLDocument located within the IFS is
read
in the Common Table Expression. It returns only the data with cod_prd =
0146030059901301460300599013 and converts the data into a relational form.
Within the final select, the result of the Common Table Expression is read
and returned as XMLDocument again.

With Rel as (Select *
from XMLTABLE('/Prestazione/DatiPdR'
passing XMLParse(Document
Get_xml_File('/home/Hauser/TestXML.xml'))
Columns "cod_pdr" Varchar(15),
"matr_mis" VarChar(20),
"coeff_corr" VarChar(15),
"freq_let" VarChar(5),
"acc_mis" VarChar(5),
"data_racc" VarChar(10),
"let_tot_prel" VarChar(10),
"tipo_lettura" Varchar(5),
"val_dato" VarChar(5),
"num_tentativi" VarChar(5),
"esito_raccolta" VarChar(5),
"mod_alt_racc" Varchar(5),
"dir_indennizzo" VarChar(5),
"pros_fin" VarChar(25)) x
Where "cod_pdr" = '01460300599013')
Select xmlGroup("cod_pdr" as "cod_pdr",
"matr_mis" as "matr_mis",
"coeff_corr" as "coeff_corr",
"freq_let" as "freq_let",
"acc_mis" as "acc_mis",
"data_racc" as "data_racc",
"let_tot_prel" as "let_tot_prel",
"tipo_lettura" as "tipo_lettura",
"val_dato" as "val_dato",
"num_tentativi" as "num_tentativi",
"esito_raccolta" as "esito_raccolata",
"mod_alt_racc" as "mod_alt_racc",
"dir_indennizzo" as "dir_indennizzo",
"pros_fin" as "pros_fin"
Option row "DatiPdr"
root "Prestazione")
from rel;


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Marco
Facchinetti
Sent: Montag, 11. Dezember 2017 12:06
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Filtering Xml using sql

Hi, we are at 7.2

We are curently processing many xml files (using Expat, thanks SK!) and we
have no problems handling all of them. But a customer asked a side order:
while processing some xml file is possible to apply a filter and generate a
new xml file based on that filter? Of course the filter is based on DB
values and not on a static statement. Here an example:

<?xml version="1.0" encoding="ISO-8859-1"?> <Prestazione cod_servizio="TML"
cod_flusso="0050">
<IdentificativiRichiesta>
<piva_utente>01948840986</piva_utente>
<piva_distr>12883450152</piva_distr>
</IdentificativiRichiesta>
<DatiPdR>
<cod_pdr>01460300599013</cod_pdr>
<matr_mis>SMGR034115193036</matr_mis>
<coeff_corr>0.937715</coeff_corr>
<freq_let>1</freq_let>
<acc_mis>1</acc_mis>
<data_racc>30/11/2017</data_racc>
<let_tot_prel>000002156</let_tot_prel>
<tipo_lettura>E</tipo_lettura>
<val_dato>SI</val_dato>
<num_tentativi>1</num_tentativi>
<esito_raccolta>P</esito_raccolta>
<mod_alt_racc>N</mod_alt_racc>
<dir_indennizzo>N</dir_indennizzo>
<pros_fin>31/05/2018 31/12/2018</pros_fin>
</DatiPdR>
<DatiPdR>
<cod_pdr>01460300600772</cod_pdr>
<matr_mis>SMGR034115179789</matr_mis>
<coeff_corr>0.937715</coeff_corr>
<freq_let>1</freq_let>
<acc_mis>1</acc_mis>
<data_racc>30/11/2017</data_racc>
<let_tot_prel>000000994</let_tot_prel>
<tipo_lettura>E</tipo_lettura>
<val_dato>SI</val_dato>
<num_tentativi>1</num_tentativi>
<esito_raccolta>P</esito_raccolta>
<mod_alt_racc>N</mod_alt_racc>
<dir_indennizzo>N</dir_indennizzo>
<pros_fin>31/05/2018 31/12/2018</pros_fin>
</DatiPdR>
</Prestazione>

The request is to apply to cod_pdr something like this:

Where cod_pdr in(select mypdr from mycustfile where active_date is not null
and ending_date is null)

and then save the result in a new xml file (assuming only pdr
01460300600772 matches):

<?xml version="1.0" encoding="ISO-8859-1"?> <Prestazione cod_servizio="TML"
cod_flusso="0050">
<IdentificativiRichiesta>
<piva_utente>01948840986</piva_utente>
<piva_distr>12883450152</piva_distr>
</IdentificativiRichiesta>
<DatiPdR>
<cod_pdr>01460300600772</cod_pdr>
<matr_mis>SMGR034115179789</matr_mis>
<coeff_corr>0.937715</coeff_corr>
<freq_let>1</freq_let>
<acc_mis>1</acc_mis>
<data_racc>30/11/2017</data_racc>
<let_tot_prel>000000994</let_tot_prel>
<tipo_lettura>E</tipo_lettura>
<val_dato>SI</val_dato>
<num_tentativi>1</num_tentativi>
<esito_raccolta>P</esito_raccolta>
<mod_alt_racc>N</mod_alt_racc>
<dir_indennizzo>N</dir_indennizzo>
<pros_fin>31/05/2018 31/12/2018</pros_fin>
</DatiPdR>
</Prestazione>

We DO NOT store xml file in our DB, we only save in a secured IFS folder.

I thought about SQL but I'm open to any solution.

TIA
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.