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
Solved

Read xml file and insert into database table

Posted on 2008-10-20
8
1,493 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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