Solved

Read xml file and insert into database table

Posted on 2008-10-20
8
1,488 Views
Last Modified: 2008-11-08
I have an xml file containing multiple records that I need to insert into a SQL2000 database table called Images. I would like to know the best way to do this when their could be a hundred records of data in the XML file.

The xml is formatted as follows:
<?xml version='1.0' encoding='utf-8'?>

<Picture>

  <PictureName></PictureName>

  <WorkOrderNo></WorkOrderNo>

  <IssueNo></IssueNo>

  <Round></Round>

  <Operative></Operative>

  <InChargeDate></InChargeDate>

  <LocationNo></LocationNo>

  <PanelNo></PanelNo>

  <WorkDate></WorkDate>

  <WorkTime></WorkTime>

  <Design></Design>

  <GPSLattitude></GPSLattitude>

  <GPSLongitude></GPSLongitude>

  <MOMSFeedback></MOMSFeedback>

</Picture>

<Picture>

  <PictureName></PictureName>

  <WorkOrderNo></WorkOrderNo>

  <IssueNo></IssueNo>

  <Round></Round>

  <Operative></Operative>

  <InChargeDate></InChargeDate>

  <LocationNo></LocationNo>

  <PanelNo></PanelNo>

  <WorkDate></WorkDate>

  <WorkTime></WorkTime>

  <Design></Design>

  <GPSLattitude></GPSLattitude>

  <GPSLongitude></GPSLongitude>

  <MOMSFeedback></MOMSFeedback>

</Picture>

Open in new window

0
Comment
Question by:clearchannel
  • 4
  • 4
8 Comments
 
LVL 1

Expert Comment

by:patricka_0377
ID: 22756666
I have used OpenXML for this in a stored procedure setting the input xml as a TEXT parameter

Create an internal representation of the document using sp_xml_preparedocument (@INT_DOC OUTPUT, @XML_DOC)
0
 

Author Comment

by:clearchannel
ID: 22756737
I just want to use ASP and VBScript for this task, no SPs.
0
 
LVL 1

Expert Comment

by:patricka_0377
ID: 22756970
i would like to see a solution just using these techs too.... but I will wait and see for responses
...
if the only tool you have is a hammer every problem is a nail
0
 

Author Comment

by:clearchannel
ID: 22758022
I am not saying only those technologies, but I would predominantly like to read the xml file contents, loop through the items insert them into a database table using asp, vbscript, an xml parser or reader and sql.
No SPs. No need for the sarcastic comments.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Expert Comment

by:patricka_0377
ID: 22758213
no sarcasm intended... I just thought it was difficult to do limiting to certain technologies... and was looking forward to see what people came up with.
0
 
LVL 1

Expert Comment

by:patricka_0377
ID: 22758263
Why no SP? I don't understand why you would not want to use these?

The architecture of a solution is something I always take great interest in as it can commonly cause massive problems at later stages if not done properly in the first instance.
0
 

Author Comment

by:clearchannel
ID: 22865604
Because I would rather not use them. I have found a solution elsewhere now.
0
 

Accepted Solution

by:
clearchannel earned 0 total points
ID: 22867573
I have used LINQ 2 SQL and the ReadXML method, creating a dataset for the data import
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

948 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

20 Experts available now in Live!

Get 1:1 Help Now