× 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.


  1.  Home
  2. MIDRANGE-L
  3. December 2017

Re: Filtering Xml using sql

+++++1 on this - the XMLTABLE SQL table function lets you filter using XQUERY clauses - you can get info on this on the Knowledge Center on XMLTABLE, I believe. With this you can get a result table, which then you can use in another XML SQL function to generate the new XML.

If you have seen any of Birgitta Hauser's presentations on this at COMMON or maybe COMMON Europe, they will help you to make sense of this whole thing.

HTH
Vern

On 12/11/2017 6:08 AM, Rob Berendt wrote:
Rather busy today but have you looked at
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafy.pdf
Starting with "SQL statements and SQL/XML functions"



Rob Berendt
-- IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive Garrett, IN 46738 Ship to: Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com From: Marco Facchinetti <marco.facchinetti@xxxxxxxxx> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> Date: 12/11/2017 06:06 AM Subject: Filtering Xml using sql Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> 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.