Link to home
Start Free TrialLog in
Avatar of clearchannel
clearchannel

asked on

Read xml file and insert into database table

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

Avatar of patricka_0377
patricka_0377

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)
Avatar of clearchannel

ASKER

I just want to use ASP and VBScript for this task, no SPs.
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
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.
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.
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.
Because I would rather not use them. I have found a solution elsewhere now.
ASKER CERTIFIED SOLUTION
Avatar of clearchannel
clearchannel

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial