Match XML data value with the mysql database

[Yogesh Khandelwal]

Hi,

I'm having one XML and one mysql database..I am able to insert the xml element data into the database.Now i want to retrive the data from database table and want to match the data with the xml element data.I have written the code for parsing XML and also written the code for fetching the data from database but not getting how do i match both..

All idea would be really appreciated.

Thanks

[Sagar Rohankar]

Yogesh Khandelwal wrote: ..... but not getting how do i match both..

If you know which xml elements match with which rows, I mean If you have any unique key to match with, you can write a query like

SELECT col_name FROM table_name WHERE col_name = 'value_from_xml_for_element_name' AND unique_id = 'from_xml';

If the result set has NO rows fetched , it means there is no entry for XML data in DB.

[Yogesh Khandelwal]

I know which xml elements match with which rows, but i don't want to do any hard coding..i am having so many database table and want to write a generic program for all.

I have attached my xml file and database columns name...and also writing this program because i don't want any dependency on XSD..

<?xml version="1.0" encoding="UTF-8"?>
<cfb-schedule-document>
<sports-statistics>
<sports-schedule>
<date year="2007" month="10" date="09" day="3"/>
<time hour="00" minute="29" timezone="Eastern"/>
<season season="2007"/>
<football-cfb-schedule>
<game-schedule>
<visiting-team>
<team-name name="Eagles" alias="NCCent"/>
<team-city city="North Carolina Central"/>
<team-code id="6121" division="2"/>
<team-conference conference="Independents (FCS)" id="74" />
<ranking ranking="0" />
</visiting-team>
<home-team>
<team-name name="Golden Rams" alias="ALBAGA"/>
<team-city city="Albany State"/>
<team-code id="357" division="3"/>
<team-conference conference="Southern Intercollegiate Athletic" id="37" />
<ranking ranking="0" />
</home-team>
<date year="2007" month="8" date="25" day="6"/>
<time hour="18" minute="00" timezone="Eastern"/>
<gamedate-day day="6" />
<tba tba="false" />
<week week="1"/>
<gamecode code="200708250357" global-id="764950"/>
<gametype type="Regular Season" />
<stadium name="Albany Municipal Coliseum" city="Albany" state="GA" id="2081" />
<bowl id="" name="" />
<status status="Final"/>
<visiting-team-score score="10"/>
<home-team-score score="16"/>
<total-quarters total="4"/>
<outcome-visit outcome="Loss" conference-outcome="No game" />
<outcome-home outcome="Win" conference-outcome="No game" />
</game-schedule>

</football-cfb-schedule>
</sports-schedule>
</sports-statistics>
</cfb-schedule-document>






column name=MODIFIED_DATETIME table=cfb_schedule
column name=MODIFIED_BY table=cfb_schedule
column name=FEED_DATETIME table=cfb_schedule
column name=SEASON table=cfb_schedule
column name=VISIT_TEAM_NAME table=cfb_schedule
column name=VISIT_TEAM_ALIAS table=cfb_schedule
column name=VISIT_TEAM_CITY table=cfb_schedule
column name=VISIT_TEAM_CODE table=cfb_schedule
column name=VISIT_TEAM_CODE_GLOBAL_ID table=cfb_schedule
column name=VISIT_TEAM_DIVISION table=cfb_schedule
column name=VISIT_TEAM_CONFERENCE table=cfb_schedule
column name=VISIT_TEAM_CONF_ID table=cfb_schedule
column name=VISIT_TEAM_RANK table=cfb_schedule
column name=HOME_TEAM_NAME table=cfb_schedule
column name=HOME_TEAM_ALIAS table=cfb_schedule
column name=HOME_TEAM_CITY table=cfb_schedule
column name=HOME_TEAM_CODE table=cfb_schedule
column name=HOME_TEAM_CODE_GLOBAL_ID table=cfb_schedule
column name=HOME_TEAM_DIVISION table=cfb_schedule
column name=HOME_TEAM_CONFERENCE table=cfb_schedule
column name=HOME_TEAM_CONF_ID table=cfb_schedule
column name=HOME_TEAM_RANK table=cfb_schedule
column name=GAME_DATETIME table=cfb_schedule
column name=LOCAL_GAME_DATE table=cfb_schedule
column name=LOCAL_TIME table=cfb_schedule
column name=TBA table=cfb_schedule
column name=WEEK table=cfb_schedule
column name=GAME_CODE table=cfb_schedule
column name=GAME_GLOB_ID table=cfb_schedule
column name=GAME_TYPE table=cfb_schedule
column name=STADIUM_NAME table=cfb_schedule
column name=STADIUM_CITY table=cfb_schedule
column name=STADIUM_STATE table=cfb_schedule
column name=STADIUM_ID table=cfb_schedule
column name=STADIUM_GLOBAL_ID table=cfb_schedule
column name=BOWL_CODE table=cfb_schedule
column name=BOWL_NAME table=cfb_schedule
column name=GAMESTATE_STATUS table=cfb_schedule
column name=VISIT_TEAM_SCORE table=cfb_schedule
column name=HOME_TEAM_SCORE table=cfb_schedule
column name=TOTAL_QUARTERS table=cfb_schedule
column name=OUTCOME_VISIT table=cfb_schedule
column name=OUTCOME_VISIT_CONFERENCE table=cfb_schedule
column name=OUTCOME_HOME table=cfb_schedule
column name=OUTCOME_HOME_CONFERENCE table=cfb_schedule

[Sagar Rohankar]

Yogesh Khandelwal wrote:I know which xml elements match with which rows, but i don't want to do any hard coding..i am having so many database table and want to write a generic program for all.

With all respects to your statement, then I don't think any other solution than "Fetch all the rows, check each column with its corresponding xml element data". Now this solution is not at all feasible and may get bigger if the number of rows increases.

One doubt, are you cross checking the same values which you just inserted into db from XML?

[Yogesh Khandelwal]

Sagar Rohankar wrote:
One doubt, are you cross checking the same values which you just inserted into db from XML?

Yeah i am just cross checking the same values which i have inserted into Db from XML..

what i think is..i can maintain one properties file and map that properties file with XML and with read the properties file and match it with DB..but this will not be a feasible solution for this....please do help me regarding this:)

[Sagar Rohankar]

Yogesh Khandelwal wrote:

Sagar Rohankar wrote:
One doubt, are you cross checking the same values which you just inserted into db from XML?

Yeah i am just cross checking the same values which i have inserted into Db from XML..

Why you cross check, Don't you have any confidence in underlying Drivers , database, etc, Java SQL API is rich enough to tell you whether he successfully inserted data or not. What this worry about ?

Yogesh Khandelwal wrote:
what i think is..i can maintain one properties file and map that properties file with XML and with read the properties file and match it with DB..but this will not be a feasible solution for this....please do help me regarding this:)

Hmm, not understood, but think about what I suggested above !!

[Yogesh Khandelwal]

i'm writing a code for QA so that when they insert data into DB they should be able to cross verify the data.If in the middle of this some has modify either XML or DB table than i don't want to insert the full data again.just will run the XML matching with database file so that we can easily able to know in which table column or XML line has been modified..

Hope you got it this time:)

Thanks

[Sagar Rohankar]

OK, We can write a generic code, but with some hard coded value to, at least , start with. (Later If successful, we can have .properties file).

We can have SWITCH statement, which decide which query to execute, like

 // the query is not completed
public final String q1 = "SELECT MODIFIED_DATETIME, MODIFIED_BY, FEED_DATETIME, .... " +
"FROM cfb_schedule WHERE MODIFIED_DATETIME = 'value_from_xml AND ......";

and on table name

switch(table_name)
{
        .
        .
         case CFB_SCEDULE: // this is constant or enum
        .
        .
}

Now we to take "table_name" extracted from the root element of xml, here it is <cfb-schedule-document>..

[Yogesh Khandelwal]

Sagar Rohankar wrote:

 // the query is not completed
public final String q1 = "SELECT MODIFIED_DATETIME, MODIFIED_BY, FEED_DATETIME, .... " +
"FROM cfb_schedule WHERE MODIFIED_DATETIME = 'value_from_xml AND ......";

and on table name

switch(table_name)
{
        .
        .
         case CFB_SCEDULE: // this is constant or enum
        .
        .
}

Now we to take "table_name" extracted from the root element of xml, here it is <cfb-schedule-document>..

Thanks Sagar,i really appreciate it..but there is one problem here..we are having lots of XML file so XML file is also gonna change..so what i have to do is..just read the XML file and in which attribute we have data,compare it with database table..so can we have a sql query which can read the XML attribute at run time...

[Sagar Rohankar]

Yogesh Khandelwal wrote:we are having lots of XML file so XML file is also gonna change..so what i have to do is..just read the XML file and in which attribute we have data,compare it with database table..so can we have a sql query which can read the XML attribute at run time...

For the same reason I suggested the switch statement, see, every time you parsed the xml, just extract the root element , here in your given file it is <cfb-schedule-document> (I suggest if possible to you, remove that "-document" from tag, and keep it simply the name of table name i.e <cfb-schedule>) Once you know in which table to looked for, you can cross check the data..

Its simple, What is the problem ?

[Yogesh Khandelwal]

Its simple, What is the problem ?

Problem is how do we get the data from XML..i already told you that i don't want to do any hard coding for that..so i am searching a solution by which i can get the data from XML..one way i know is by using JAXB component but it's require XSD file so that i can create getter/setter method but i don't want any dependencies on XSDs so please provide me a solution by which i can access the xml attribute and compare it with database..

[Sagar Rohankar]

Yogesh Khandelwal wrote:
Problem is how do we get the data from XML..

Parse it.

[Yogesh Khandelwal]

Sagar Rohankar wrote:
Parse it.

Sir,i'm able to parse the XML but not getting how do i make sql query by getting the data from XML element and tag.Is there any get/set method for this..

[Sagar Rohankar]

Yogesh Khandelwal wrote:

Sagar Rohankar wrote:
Parse it.

Sir,i'm able to parse the XML but not getting how do i make sql query by getting the data from XML element and tag.Is there any get/set method for this..

I don't know about any get/set method, I know only SAX or DOM method of parsing. And to make the query, I suggest something like this,

1. Parse the root , which gives you the table name for query.
2. Extract all the element which are mapped with db column names, put them into variable, like SEASON tag,

String season = parser.getElementAttribute("season");

3. Make query,

    SELECT SEASON FROM " + table_name + " WHERE SEASON = " + season +";"

[Yogesh Khandelwal]

Isn't this hardcode?

still the problem is there.I am able to parse the XML and getting all the data which i have in XML
Parsing XML file... c:/CFB_SCHEDULE_ALL_1.xml
XML file parsed
Statement of XML document...
NodeName: cfb-schedule-document, NodeValue:

NodeName: sports-statistics, NodeValue:

NodeName: sports-schedule, NodeValue:

NodeName: date, NodeValue:
AttributeName: date, attributeValue: 09
AttributeName: day, attributeValue: 3
AttributeName: month, attributeValue: 10
AttributeName: year, attributeValue: 2007
NodeName: time, NodeValue:
AttributeName: hour, attributeValue: 00
AttributeName: minute, attributeValue: 29
AttributeName: timezone, attributeValue: Eastern
NodeName: season, NodeValue:
AttributeName: season, attributeValue: 2007
NodeName: football-cfb-schedule, NodeValue:

NodeName: game-schedule, NodeValue:

NodeName: visiting-team, NodeValue:

NodeName: team-name, NodeValue:
AttributeName: alias, attributeValue: NCCent
AttributeName: name, attributeValue: Eagles
NodeName: team-city, NodeValue:
AttributeName: city, attributeValue: North Carolina Central
NodeName: team-code, NodeValue:
AttributeName: division, attributeValue: 2
AttributeName: id, attributeValue: 6121
NodeName: team-conference, NodeValue:
AttributeName: conference, attributeValue: Independents (FCS)
AttributeName: id, attributeValue: 74
NodeName: ranking, NodeValue:
AttributeName: ranking, attributeValue: 0
NodeName: home-team, NodeValue:

NodeName: team-name, NodeValue:
AttributeName: alias, attributeValue: ALBAGA
AttributeName: name, attributeValue: Golden Rams
NodeName: team-city, NodeValue:
AttributeName: city, attributeValue: Albany State
NodeName: team-code, NodeValue:
AttributeName: division, attributeValue: 3
AttributeName: id, attributeValue: 357
NodeName: team-conference, NodeValue:
AttributeName: conference, attributeValue: Southern Intercollegiate Athletic
AttributeName: id, attributeValue: 37
NodeName: ranking, NodeValue:
AttributeName: ranking, attributeValue: 0
NodeName: date, NodeValue:
AttributeName: date, attributeValue: 25
AttributeName: day, attributeValue: 6
AttributeName: month, attributeValue: 8
AttributeName: year, attributeValue: 2007
NodeName: time, NodeValue:
AttributeName: hour, attributeValue: 18
AttributeName: minute, attributeValue: 00
AttributeName: timezone, attributeValue: Eastern
NodeName: gamedate-day, NodeValue:
AttributeName: day, attributeValue: 6
NodeName: tba, NodeValue:
AttributeName: tba, attributeValue: false
NodeName: week, NodeValue:
AttributeName: week, attributeValue: 1
NodeName: gamecode, NodeValue:
AttributeName: code, attributeValue: 200708250357
AttributeName: global-id, attributeValue: 764950
NodeName: gametype, NodeValue:
AttributeName: type, attributeValue: Regular Season
NodeName: stadium, NodeValue:
AttributeName: city, attributeValue: Albany
AttributeName: id, attributeValue: 2081
AttributeName: name, attributeValue: Albany Municipal Coliseum
AttributeName: state, attributeValue: GA
NodeName: bowl, NodeValue:
AttributeName: id, attributeValue:
AttributeName: name, attributeValue:
NodeName: status, NodeValue:
AttributeName: status, attributeValue: Final
NodeName: visiting-team-score, NodeValue:
AttributeName: score, attributeValue: 10
NodeName: home-team-score, NodeValue:
AttributeName: score, attributeValue: 16
NodeName: total-quarters, NodeValue:
AttributeName: total, attributeValue: 4
NodeName: outcome-visit, NodeValue:
AttributeName: conference-outcome, attributeValue: No game
AttributeName: outcome, attributeValue: Loss
NodeName: outcome-home, NodeValue:
AttributeName: conference-outcome, attributeValue: No game
AttributeName: outcome, attributeValue: Win
... end of statement

also able to fetch the column name and data from database table but now got stuck with mapping with the MXL tags and attribute.How can i map it with XML tag and attributes.

Thanks for the help:)

[Sagar Rohankar]

Yogesh Khandelwal wrote:Isn't this hardcode?

If you think so, then Yes, But I want atleast a code which make your objectve clear, and then we think to minimze the dependecny.

Yogesh Khandelwal wrote:
also able to fetch the column name and data from database table but now got stuck with mapping with the MXL tags and attribute.How can i map it with XML tag and attributes.

What is mapping between tags and attribute, as I can see you can extract the all the tags with there respective attribute.. So you can write a bunch of if..else statement to get the value , for example "season"..

NodeName: season, NodeValue:
AttributeName: season, attributeValue: 2007

String season;
if(NodeName == "season")
{
        season = getAttributeName("season").getAttrbValue();
}

And don't call me Sir, we Ranchers are friends here

[Yogesh Khandelwal]

Sagar Rohankar wrote:
What is mapping between tags and attribute, as I can see you can extract the all the tags with there respective attribute.. So you can write a bunch of if..else statement to get the value , for example "season"..

NodeName: season, NodeValue:
AttributeName: season, attributeValue: 2007

String season;
if(NodeName == "season")
{
        season = getAttributeName("season").getAttrbValue();
}

And don't call me Sir, we Ranchers are friends here

But it's gonna be huge if i have lots of tags present in XML..i heard something about Xquery which can resolve that problem but don't know how do we make the xquery..researching on it..but think the other possible solution for that problem..Thanks:)