Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

UPDATE A DATABASE TABLE WITH AN XML FILE... NEWBIE HELP!

Posted on 2004-09-16
22
Medium Priority
?
171 Views
Last Modified: 2010-04-23
I have an XML file named "categories.xml".
Its structure is something like this:

<root>
<cat CatId="1" CatDesc="Something" Active="1" Path="whatever">
</root>

This file is constantly being edited throughout the day (edits, additions etc).  I need to update a SQL Server database table with these changes nightly.  

The fields in the database table match up perfectly to the XML file with the exception that the database table has two extra fields: PrimaryKey and ForeignKey.  I'm most familiar with VB .NET if you can show me a way to do this programmatically.  The goal is to write a console application that will be scheduled to run nightly.  Any help will be greatly appreciated.  I'll raise the point values if it proves to be more difficult than anticipated.  Thanks!

How do I go out about doing this?  Is it something with XDR? XLST?  What are these?
0
Comment
Question by:mbosch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
22 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12077160
Is it only multiple lines of text, like:

<cat CatId="1" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="2" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="3" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="4" CatDesc="Something" Active="1" Path="whatever">

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12077743
That's exactly what it looks like.  Well except there's that one <root>...</root> but thats how it looks.

ie
<root>
<cat CatId="1" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="2" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="3" CatDesc="Something" Active="1" Path="whatever">
<cat CatId="4" CatDesc="Something" Active="1" Path="whatever">
</root>


Thanks!
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12077767
And what information do you want from each line?

Bob
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:mbosch
ID: 12077798
All of it.  The database table has exactly the same fields as attributes (with the exception of having a PrimaryKey and ForeignKey column).  I need to update the table with the values from the XML file.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12078016
Something like this should work:

  Public Sub ReadXMLAsDataSet(ByVal fileName As String)

    Try

      Dim ds As DataSet = New DataSet(Path.GetFileNameWithoutExtension(fileName))

      ds.ReadXml(fileName)

      ...

    Catch ex As Exception

      MessageBox.Show(ex.ToString)

    End Try

  End Sub


The dataset will have 1 table with the same number of columns as attributes, and the same number of rows as <cat> lines.

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12078090
Right, I got that far.  I was able to read it and then open a datalist with the dataset as the datasouce and it showed everything.  So I know it loaded the data.  But now I don't know how to update the table.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12078110
What are you having a problem with?  Do you understand ADO.NET in relation to SQL Server?

SQLDataAdapter
SQLConnection
SQLCommand
...

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12078174
Here's where I got too      
 Try
            Dim conn As SqlConnection = New SqlConnection("data source=MBOSCH;initial catalog=tiger;integrated security=SSPI;persist security info=False;workstation id=MBOSCH;packet size=4096")

            Dim ds As DataSet = New DataSet()

            ds.ReadXml("C:\categories.xml", XmlReadMode.InferSchema)

            'Dim i As Integer

            'For i = 0 To ds.Tables.Count
            'MessageBox.Show(ds.Tables(i).TableName())
            'Next

            'mydgrid.DataSource = ds.Tables(0)

            'Create the sql adapater to the data

            Dim tblAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM webcat", conn)

            Dim tblCommand As SqlCommandBuilder = New SqlCommandBuilder(tblAdapter)

            tblAdapter.Update(ds.Tables(0))

        Catch ex As Exception

        End Try

This is as far as I got, the it just freezes at the tblAdapter.  I'm kind of new at this.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12078243
You're not doing too badly.  


Where did the connection string come from?  Is it a valid connection?  


Also, add this in the Catch block of the Try...Catch:

Catch ex As Exception

    MessageBox.Show(ex.ToString)


With the command builder, you are going to need an InsertCommand for the data adapter to make the necessary updates from the dataset.

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12084856
These are the commands it builds:  Then it tells me it can't insert duplicate primary keys.  I'm so confused!

*** INSERT ***
INSERT INTO webcat( AOL , ActiveCA , ActiveUS , Alias , AmazonCatId , Assign , CatDesc , CatId , CatLevel , EDICatId , H2fMain , H2fRecert , Keywords , Path , Popup , RedirectIdCA , RedirectIdUS , RedirectURLCA , RedirectURLUS , Sort , SpecAv , Warranty ) VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 , @p9 , @p10 , @p11 , @p12 , @p13 , @p14 , @p15 , @p16 , @p17 , @p18 , @p19 , @p20 , @p21 , @p22 )
*** UPDATE ***
UPDATE webcat SET AOL = @p1 , ActiveCA = @p2 , ActiveUS = @p3 , Alias = @p4 , AmazonCatId = @p5 , Assign = @p6 , CatDesc = @p7 , CatId = @p8 , CatLevel = @p9 , EDICatId = @p10 , H2fMain = @p11 , H2fRecert = @p12 , Keywords = @p13 , Path = @p14 , Popup = @p15 , RedirectIdCA = @p16 , RedirectIdUS = @p17 , RedirectURLCA = @p18 , RedirectURLUS = @p19 , Sort = @p20 , SpecAv = @p21 , Warranty = @p22 WHERE ( ((AOL IS NULL AND @p23 IS NULL) OR (AOL = @p24)) AND ((ActiveCA IS NULL AND @p25 IS NULL) OR (ActiveCA = @p26)) AND ((ActiveUS IS NULL AND @p27 IS NULL) OR (ActiveUS = @p28)) AND ((Alias IS NULL AND @p29 IS NULL) OR (Alias = @p30)) AND ((AmazonCatId IS NULL AND @p31 IS NULL) OR (AmazonCatId = @p32)) AND ((Assign IS NULL AND @p33 IS NULL) OR (Assign = @p34)) AND ((CatDesc IS NULL AND @p35 IS NULL) OR (CatDesc = @p36)) AND (CatId = @p37) AND ((CatLevel IS NULL AND @p38 IS NULL) OR (CatLevel = @p39)) AND ((EDICatId IS NULL AND @p40 IS NULL) OR (EDICatId = @p41)) AND ((H2fMain IS NULL AND @p42 IS NULL) OR (H2fMain = @p43)) AND ((H2fRecert IS NULL AND @p44 IS NULL) OR (H2fRecert = @p45)) AND ((Keywords IS NULL AND @p46 IS NULL) OR (Keywords = @p47)) AND ((Path IS NULL AND @p48 IS NULL) OR (Path = @p49)) AND ((Popup IS NULL AND @p50 IS NULL) OR (Popup = @p51)) AND ((RedirectIdCA IS NULL AND @p52 IS NULL) OR (RedirectIdCA = @p53)) AND ((RedirectIdUS IS NULL AND @p54 IS NULL) OR (RedirectIdUS = @p55)) AND ((RedirectURLCA IS NULL AND @p56 IS NULL) OR (RedirectURLCA = @p57)) AND ((RedirectURLUS IS NULL AND @p58 IS NULL) OR (RedirectURLUS = @p59)) AND ((Sort IS NULL AND @p60 IS NULL) OR (Sort = @p61)) AND ((SpecAv IS NULL AND @p62 IS NULL) OR (SpecAv = @p63)) AND ((Warranty IS NULL AND @p64 IS NULL) OR (Warranty = @p65)) )
*** DELETE ***
DELETE FROM  webcat WHERE ( ((AOL IS NULL AND @p1 IS NULL) OR (AOL = @p2)) AND ((ActiveCA IS NULL AND @p3 IS NULL) OR (ActiveCA = @p4)) AND ((ActiveUS IS NULL AND @p5 IS NULL) OR (ActiveUS = @p6)) AND ((Alias IS NULL AND @p7 IS NULL) OR (Alias = @p8)) AND ((AmazonCatId IS NULL AND @p9 IS NULL) OR (AmazonCatId = @p10)) AND ((Assign IS NULL AND @p11 IS NULL) OR (Assign = @p12)) AND ((CatDesc IS NULL AND @p13 IS NULL) OR (CatDesc = @p14)) AND (CatId = @p15) AND ((CatLevel IS NULL AND @p16 IS NULL) OR (CatLevel = @p17)) AND ((EDICatId IS NULL AND @p18 IS NULL) OR (EDICatId = @p19)) AND ((H2fMain IS NULL AND @p20 IS NULL) OR (H2fMain = @p21)) AND ((H2fRecert IS NULL AND @p22 IS NULL) OR (H2fRecert = @p23)) AND ((Keywords IS NULL AND @p24 IS NULL) OR (Keywords = @p25)) AND ((Path IS NULL AND @p26 IS NULL) OR (Path = @p27)) AND ((Popup IS NULL AND @p28 IS NULL) OR (Popup = @p29)) AND ((RedirectIdCA IS NULL AND @p30 IS NULL) OR (RedirectIdCA = @p31)) AND ((RedirectIdUS IS NULL AND @p32 IS NULL) OR (RedirectIdUS = @p33)) AND ((RedirectURLCA IS NULL AND @p34 IS NULL) OR (RedirectURLCA = @p35)) AND ((RedirectURLUS IS NULL AND @p36 IS NULL) OR (RedirectURLUS = @p37)) AND ((Sort IS NULL AND @p38 IS NULL) OR (Sort = @p39)) AND ((SpecAv IS NULL AND @p40 IS NULL) OR (SpecAv = @p41)) AND ((Warranty IS NULL AND @p42 IS NULL) OR (Warranty = @p43)) )
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12084885
What is the table structure like?  Do you have multiple primary keys?

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12086082
This is the table structure:

PrimaryKey|ForeignKey|AOL|ActiveCA|ActiveUS|Alias|AmazonCatId|Assign|CatDesc|CatId|CatLevel|EDICatId|H2fMain|H2fRecert|Keywords|Path|Popup||RedirectIdCA|RedirectIdUS|RedirectURLCA|RedirectURLUS|Sort|SpecAv|Warranty
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12086103
Oh, wait, I think that I understand.  You got an error that the insert would create duplicate primary keys, right?  What fields are involved in the primary key?

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12086184
I think the only key fields are the first PrimaryKey.  But I dont' understand something about it.  The XML file doesn't have the PrimaryKey and ForeignKey fields, it has every other one though and matches directly to the table.  I posted both files here:  

The XML:
http://www.campusdrunk.com/categories.xml

The database structure:
http://www.campusdrunk.com/WEBCAT.csv

Thanks for all your help!  I'm desperate, I will increase points if you can help me!
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12086239
Is the PrimaryKey column an Identity column in the SQL server table?

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12086259
I know that the file is recursive and the PK's and FK's are generated based on the parent/child relationship.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12086269
Do you understand what I meant by my last question?

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12086314
sorry, yes it is an identity column.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 12103316
Did you have any progress with this one?

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12103549
Somewhat.  Is this what's suppose to happen?

READ THE XML FILE INTO A DATASET

CREATE A NEW BLANK DATASET

STARTING WITH THE FIRST RECORD IN THE XML FILE, BEGIN INPUTTING EACH RECORD INTO THE BLANK DATASET

AT THE END, UPDATE THE SQL TABLE WITH THE INFO IN THE NEW (NOW FILLED) DATASET

The syntax of doing this is where I'm going to get lost...  Ya busy today? :-)
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 12103616
No, I would suggest reading the XML file into a dataset, and then transfer them directly to SQL Server updating the PrimaryKey and ForeignKey as necessary to get the recursive relationship.

Bob
0
 
LVL 1

Author Comment

by:mbosch
ID: 12127698
THanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Screencast - Getting to Know the Pipeline
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question