Re: Filtering Xml using sql

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