Solved

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

Posted on 2004-09-16
22
163 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
  • 11
  • 11
22 Comments
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
Comment Utility
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
Comment Utility
And what information do you want from each line?

Bob
0
 
LVL 1

Author Comment

by:mbosch
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What is the table structure like?  Do you have multiple primary keys?

Bob
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:mbosch
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is the PrimaryKey column an Identity column in the SQL server table?

Bob
0
 
LVL 1

Author Comment

by:mbosch
Comment Utility
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
Comment Utility
Do you understand what I meant by my last question?

Bob
0
 
LVL 1

Author Comment

by:mbosch
Comment Utility
sorry, yes it is an identity column.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
Did you have any progress with this one?

Bob
0
 
LVL 1

Author Comment

by:mbosch
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
THanks!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now