xml parsing for dynamic sql

[if4848]

Hi,

I want to extract dynamic sql from xml file, but I do not know how to do it. Here is a xml file.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="nameSearch">

<select id="searchByNameAndBirthDate" parameterClass="map" resultMap="NameSearchResult">
Select
'RS_nameSearch_002' "queryId",
nameOffice.indv_id,
nameOffice.lst_name as lst_name,
nameOffice.fst_name as fst_name,
ids.brth_dte as brth_dte,
ids.ssn as ssn,
ids.str_adr as str_adr,
ids.city as city,
ids.st as st,
RTRIM(CHAR(ids.sub_agcy_id)) as sub_agcy_id
from (Select Distinct ins.indv_id,
lst_name,
fst_name,
mid_name,
brth_dte,
up_lst_name,
up_fst_name,
ins.sub_agcy_id
from dhsdb2.indv_name_srch INS
join dhsdb2.indv_ofc_srch IOS on ins.indv_id = ios.indv_id
join dhsdb2.indv_demg_Srch ids on ins.indv_id = ids.indv_id
where
<isNotEmpty property="lastName">
UP_LST_NAME = #lastName#
</isNotEmpty>
<isNotEmpty property="lastNameLike">
UP_LST_NAME like #lastNameLike#
</isNotEmpty>
<isNotEmpty property="lastNameSndx">
lst_name_sndx = #lastNameSndx#
</isNotEmpty>

<isNotEmpty prepend="and" property="firstName">
UP_FST_NAME = #firstName#
</isNotEmpty>
<isNotEmpty prepend="and" property="firstNameLike">
UP_FST_NAME like #firstNameLike#
</isNotEmpty>
<isNotEmpty prepend="and" property="subAgencyId">
ins.sub_agcy_id = #subAgencyId#
</isNotEmpty>
<isNotEmpty prepend="and" property="birthDate">
ids.brth_dte= #birthDate#
</isNotEmpty>
) as nameOffice
left outer join dhsdb2.indv_demg_Srch ids on nameOffice.indv_id = ids.indv_id
order by Up_lst_name, up_fst_name with UR
</select>
</sqlMap>


I want to have to have the result as
Select
'RS_nameSearch_002' "queryId",
nameOffice.indv_id,
nameOffice.lst_name as lst_name,
nameOffice.fst_name as fst_name,
ids.brth_dte as brth_dte,
ids.ssn as ssn,
ids.str_adr as str_adr,
ids.city as city,
ids.st as st,
RTRIM(CHAR(ids.sub_agcy_id)) as sub_agcy_id
from (Select Distinct ins.indv_id,
lst_name,
fst_name,
mid_name,
brth_dte,
up_lst_name,
up_fst_name,
ins.sub_agcy_id
from dhsdb2.indv_name_srch INS
join dhsdb2.indv_ofc_srch IOS on ins.indv_id = ios.indv_id
join dhsdb2.indv_demg_Srch ids on ins.indv_id = ids.indv_id
where
<isNotEmpty property="lastName">
UP_LST_NAME = #lastName#
</isNotEmpty>
<isNotEmpty property="lastNameLike">
UP_LST_NAME like #lastNameLike#
</isNotEmpty>
<isNotEmpty property="lastNameSndx">
lst_name_sndx = #lastNameSndx#
</isNotEmpty>

<isNotEmpty prepend="and" property="firstName">
UP_FST_NAME = #firstName#
</isNotEmpty>
<isNotEmpty prepend="and" property="firstNameLike">
UP_FST_NAME like #firstNameLike#
</isNotEmpty>
<isNotEmpty prepend="and" property="subAgencyId">
ins.sub_agcy_id = #subAgencyId#
</isNotEmpty>
<isNotEmpty prepend="and" property="birthDate">
ids.brth_dte= #birthDate#
</isNotEmpty>
) as nameOffice
left outer join dhsdb2.indv_demg_Srch ids on nameOffice.indv_id = ids.indv_id
order by Up_lst_name, up_fst_name with UR


Any one can give a xml parsing script to get the sql as above?
Each time, I will receive more than 100 xml files and there are more 1000 sql statements in it. I do not how to extract the dynamic sqls from the xml files.

Thanks,
Jim

[gandalf]

Please: how is this related to Cacti?
Reinhard

[if4848]

Before we move the application to production, we need to explain all sql in the xml files. The first we need to extract sql in the xml file. This is my request comes from.