?
Solved

Read xml file and insert into database table

Posted on 2008-10-20
8
Medium Priority
?
1,505 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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
 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses

607 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