Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1501
  • Last Modified:

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

0
clearchannel
Asked:
clearchannel
  • 4
  • 4
1 Solution
 
patricka_0377Commented:
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
 
clearchannelAuthor Commented:
I just want to use ASP and VBScript for this task, no SPs.
0
 
patricka_0377Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
clearchannelAuthor Commented:
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
 
patricka_0377Commented:
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
 
patricka_0377Commented:
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
 
clearchannelAuthor Commented:
Because I would rather not use them. I have found a solution elsewhere now.
0
 
clearchannelAuthor Commented:
I have used LINQ 2 SQL and the ReadXML method, creating a dataset for the data import
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now