Insert XML to Access 2007 using ASP

Hi all, I have seen this done many of times, how ever no idea how to make it work.. I have read alot of people having issues (which they have some understanding) and tutorials, unfortunately I cannot find anywhere a simple example to work off

Attached is the XML file I want to import

In this file (booking) i would want to insert into 2 tables

Basics:
<Identification Company="AE" BookingNo="604433"/>
<ReservationDetails BookingStatus="BOOKING"  DepartureDate="2015-09-01">
These would go into a table called: enq_land
under: land_supplier, supplier_ref, land_process, land_findate (respectively)

after this, all the items (hotels,transfers,etc) will need to be put into a different table reffing back to enq_land

I have the basics setup for the selection of the file to insert, but do not know how to push it through a form to be read

<input type=file name=xmlfile value=Browse accept=".xml">&nbsp;<input type=submit name=AddXML value="Import XML">

Open in new window


Any help is appreciated for this enormous task.

Cheers
bkgEnquiry.xml
Graemewebber4technologiesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Are you trying to save the xml into a memo field?  Or are you trying to grab the individual data and place in separate fields meaning you have to read the xml via asp?

To place the xml in a memo field, you would read the return line by line and concatenate to a variable.

dim xml
xml=""
xml=xml&"<Identification Company='AE' BookingNo='604433'/>"
xml=xml&"<ReservationDetails BookingStatus='BOOKING'  DepartureDate='2015-09-01'>"

Open in new window


Then insert the variable, "xml" into your memo field.

If you need to read each individual node and extract the data that is something else.
Graemewebber4technologiesAuthor Commented:
Hi there, I am trying to grab the individual data and insert into separate fields
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Ok, the first thing is to look at the xml in a pretty format.
<?xml version="1.0"?>
<BookingInfo Type="WholesaleBooking" SchemaVersion="2.0" CalypsoVersion="80">
    <Identification Company="AE" BookingNo="604433"/>
    <ReservationDetails BookingStatus="BOOKING" AgentCode="92755755" ProductRange="DAAA" CreatedDate="2015-03-11" DepartureDate="2015-08-04">
        <Consultant Number="10164" Name="GRAEME MCGILVRAY"/>
    </ReservationDetails>
    <Capabilities ItemisedPricing="CbiOnly"/>
    <Currency Code="AUD" DecimalPlaces="2"/>
    <Prices Land="2720.00" Total="2720.00"/>
    <Commission>
        <Amount Land="326.40" Total="326.40"/>
        <Rate Land="12.00"/>
    </Commission>
    <EstimatedCanxFees CanxDate="2015-04-11" TotalCanxFees="330.00"/>
    <Passengers AdultCount="2" ChildCount="0" InfantCount="0">
        <Passenger RoomNo="1" Type="ADULT">
            <Name>
                <Title>MR</Title>
                <FirstName>HAROLD</FirstName>
                <LastName>YOUNG</LastName>
            </Name>
            <Prices Land="1360.00" Total="1360.00"/>
        </Passenger>
        <Passenger RoomNo="1" Type="ADULT">
            <Name>
                <Title>MR</Title>
                <FirstName>MARIE</FirstName>
                <LastName>YOUNG</LastName>
            </Name>
            <Prices Land="1360.00" Total="1360.00"/>
        </Passenger>
    </Passengers>
    <Itinerary>
        <Trf>
            <ProductDetails Code="AHPCT" Category="PVT">
                <Description>a Private Vehicle transfer</Description>
                <AgencyDescription>Private Vehicle: SIN Airport-City Hotels</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>TOUR EAST SINGAPORE</Name>
                <Address>
                    <Line1>1 Magazine Road</Line1>
                    <Line2>Central Mall Office Tower</Line2>
                    <Line3>#07-05/06 SINGAPORE 059567</Line3>
                </Address>
                <PhoneNumber>0011 65 6735 1221</PhoneNumber>
            </Supplier>
            <Departs Date="2015-08-04">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Departs>
            <Arrives Date="2015-08-04">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Arrives>
            <Status>HK</Status>
            <Price>84.00</Price>
            <ItineraryText>
                <Text>Only valid from Singapore Airport to City hotels.</Text>
                <Text>TERMINAL 1, 2 &amp; 3:</Text>
                <Text>Upon leaving the arrival hall, please look for The Meet &amp; Greet</Text>
                <Text>representative who will be holding a TOUR EAST meeting board</Text>
                <Text/>
                <Text>If for some reason a meeting does not take place, passengers can enquire</Text>
                <Text>on the whereabout of Tour East representative from:</Text>
                <Text>- The Airport Information Desk.</Text>
                <Text>- Terminal 1: Counter GTC 5, across from Belt 20-23</Text>
                <Text>- Terminal 2: Counter CTR 15, across from Belt 36-37</Text>
                <Text>- Terminal 3: Counter 6, across from Belt 41</Text>
                <Text/>
                <Text>* Waiting time: 1 hour after flight lands. In the event you are</Text>
                <Text>held up in the immigration, please contact Tour East Singapore.</Text>
            </ItineraryText>
        </Trf>
        <Htl>
            <ProductDetails Code="CPCQB" Category="4*">
                <Description>a Deluxe Room</Description>
                <AgencyDescription>Deluxe Room - Breakfast        Singapore</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>PARK HOTEL CLARKE QUAY</Name>
                <Address>
                    <Line1>1 Unity Street</Line1>
                    <Line2>Singapore 237983</Line2>
                </Address>
                <PhoneNumber>0011 65 6593 8833</PhoneNumber>
            </Supplier>
            <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            <StayDetails CheckinDate="2015-08-04" CheckoutDate="2015-08-06" CheckoutTime="12:00" Rooms="1" Duration="2"/>
            <Meals>
                <Description>American Breakfast</Description>
            </Meals>
            <Status>HK</Status>
            <Price>780.00</Price>
        </Htl>
        <Trf>
            <ProductDetails Code="HAPCT" Category="PVT">
                <Description>a Private Vehicle transfer</Description>
                <AgencyDescription>Private Vehicle: City Hotels-SIN Airport</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>TOUR EAST SINGAPORE</Name>
                <Address>
                    <Line1>1 Magazine Road</Line1>
                    <Line2>Central Mall Office Tower</Line2>
                    <Line3>#07-05/06 SINGAPORE 059567</Line3>
                </Address>
                <PhoneNumber>0011 65 6735 1221</PhoneNumber>
            </Supplier>
            <Departs Date="2015-08-06">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Departs>
            <Arrives Date="2015-08-06">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Arrives>
            <Status>HK</Status>
            <Price>84.00</Price>
            <ItineraryText>
                <Text>Only valid from City hotels to Singapore Airport</Text>
            </ItineraryText>
        </Trf>
        <Oa>
            <From Date="2015-08-06">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </From>
            <To Date="2015-09-11">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </To>
            <Comment>OWN ARRANGEMENTS</Comment>
        </Oa>
        <Trf>
            <ProductDetails Code="AHPCT" Category="PVT">
                <Description>a Private Vehicle transfer</Description>
                <AgencyDescription>Private Vehicle: SIN Airport-City Hotels</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>TOUR EAST SINGAPORE</Name>
                <Address>
                    <Line1>1 Magazine Road</Line1>
                    <Line2>Central Mall Office Tower</Line2>
                    <Line3>#07-05/06 SINGAPORE 059567</Line3>
                </Address>
                <PhoneNumber>0011 65 6735 1221</PhoneNumber>
            </Supplier>
            <Departs Date="2015-09-11">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Departs>
            <Arrives Date="2015-09-11">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Arrives>
            <Status>HK</Status>
            <Price>84.00</Price>
            <ItineraryText>
                <Text>Only valid from Singapore Airport to City hotels.</Text>
                <Text>TERMINAL 1, 2 &amp; 3:</Text>
                <Text>Upon leaving the arrival hall, please look for The Meet &amp; Greet</Text>
                <Text>representative who will be holding a TOUR EAST meeting board</Text>
                <Text/>
                <Text>If for some reason a meeting does not take place, passengers can enquire</Text>
                <Text>on the whereabout of Tour East representative from:</Text>
                <Text>- The Airport Information Desk.</Text>
                <Text>- Terminal 1: Counter GTC 5, across from Belt 20-23</Text>
                <Text>- Terminal 2: Counter CTR 15, across from Belt 36-37</Text>
                <Text>- Terminal 3: Counter 6, across from Belt 41</Text>
                <Text/>
                <Text>* Waiting time: 1 hour after flight lands. In the event you are</Text>
                <Text>held up in the immigration, please contact Tour East Singapore.</Text>
            </ItineraryText>
        </Trf>
        <Htl>
            <ProductDetails Code="CPCQC" Category="4*">
                <Description>an Upgrade to a Deluxe Room</Description>
                <AgencyDescription>Upgrade to Deluxe Room - Breakfast   SIN</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>PARK HOTEL CLARKE QUAY</Name>
                <Address>
                    <Line1>1 Unity Street</Line1>
                    <Line2>Singapore 237983</Line2>
                </Address>
                <PhoneNumber>0011 65 6593 8833</PhoneNumber>
            </Supplier>
            <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            <StayDetails CheckinDate="2015-09-10" CheckoutDate="2015-09-15" CheckoutTime="12:00" Rooms="1" Duration="5" PriorNight="true"/>
            <Meals>
                <Description>American Breakfast</Description>
            </Meals>
            <DurationRestrictions Minimum="4"/>
            <Status>HK</Status>
            <Price>1604.00</Price>
            <BookingRemark>
                <Text>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -</Text>
                <Text>UPGRADE OFFER</Text>
                <Text>Stay a minimum of 4 consecutive nights and receive a complimentary</Text>
                <Text>upgrade from a Superior Room to a Deluxe Room.</Text>
                <Text>Conditions:</Text>
                <Text>* Valid for stays between 20 June and 16 September 2015.</Text>
                <Text>* Not valid during Event periods and black-out dates.</Text>
                <Text>* Not combinable with other offers.</Text>
            </BookingRemark>
            <ItineraryText>
                <Text>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -</Text>
                <Text>UPGRADE OFFER</Text>
                <Text>Stay a minimum of 4 consecutive nights and receive a complimentary</Text>
                <Text>upgrade from a Superior Room to a Deluxe Room.</Text>
                <Text>Conditions:</Text>
                <Text>* Valid for stays between 20 June and 16 September 2015.</Text>
                <Text>* Not valid during Event periods and black-out dates.</Text>
                <Text>* Not combinable with other offers.</Text>
            </ItineraryText>
            <VoucherText>
                <Text>- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -</Text>
                <Text>UPGRADE OFFER</Text>
                <Text>Stay a minimum of 4 consecutive nights and receive a complimentary</Text>
                <Text>upgrade from a Superior Room to a Deluxe Room.</Text>
                <Text>Conditions:</Text>
                <Text>* Valid for stays between 20 June and 16 September 2015.</Text>
                <Text>* Not valid during Event periods and black-out dates.</Text>
                <Text>* Not combinable with other offers.</Text>
            </VoucherText>
        </Htl>
        <Trf>
            <ProductDetails Code="HAPCT" Category="PVT">
                <Description>a Private Vehicle transfer</Description>
                <AgencyDescription>Private Vehicle: City Hotels-SIN Airport</AgencyDescription>
            </ProductDetails>
            <Supplier>
                <Name>TOUR EAST SINGAPORE</Name>
                <Address>
                    <Line1>1 Magazine Road</Line1>
                    <Line2>Central Mall Office Tower</Line2>
                    <Line3>#07-05/06 SINGAPORE 059567</Line3>
                </Address>
                <PhoneNumber>0011 65 6735 1221</PhoneNumber>
            </Supplier>
            <Departs Date="2015-09-15">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Departs>
            <Arrives Date="2015-09-15">
                <City Code="SIN" Name="SINGAPORE" Country="SG"/>
            </Arrives>
            <Status>HK</Status>
            <Price>84.00</Price>
            <ItineraryText>
                <Text>Only valid from City hotels to Singapore Airport</Text>
            </ItineraryText>
        </Trf>
    </Itinerary>
    <Notes>
        <Text>Quote created</Text>
        <Text>Booking remarks for H/SIN/CPCQC on 10Sep to 15Sep</Text>
        <Text>Option created</Text>
        <Text>GRAEME MCGILVRAY authorised payment of 330.00</Text>
    </Notes>
</BookingInfo>

Open in new window

Next, you need to determine which data you want and grab it one at a time.  I have a very good example from very similar question http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/Q_28451793.html

Look that over and I think you will see how you can apply to your own xml.  Give it a try and report back any problems you are having.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Graemewebber4technologiesAuthor Commented:
Fantastic! I will get this set up..

How do I pass the file i want the code to read? eg upload?
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Does the file already exist somewhere or is it something you are uploading?
Graemewebber4technologiesAuthor Commented:
uploading
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Let's start with just working on the xml file for now.  Just manually upload the xml file to your server and we can work on that part last.  Make sure you can surf to the xml file at http://yourdomain.com/bkgEnquiry.xml and just work with that.
Graemewebber4technologiesAuthor Commented:
Ok its manually loaded up there www.activenquiry.info/bkgEnquiry.xml

I have loaded the code you have suggested from your previous question, the top part makes sense, setting the XML up. However the bottom part is all about currency...
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This will get you started
<%

URL = "http://www.activenquiry.info/bkgEnquiry.xml"
Set objXML = Server.CreateObject("MSXML2.DOMDocument") ' don't add version!  let windows pick it

objXML.setProperty "ServerHTTPRequest", True
objXML.async =  False
objXML.Load(URL)

Set BookingInfo = objXML.getElementsByTagName("BookingInfo")
Response.Write "Found " & BookingInfo.Length & " nodes<hr>"

For b = 0 To BookingInfo.Length - 1
	Set booking = BookingInfo.Item(b)
	response.write booking.xml
next

%>

Open in new window

And this will help too https://technet.microsoft.com/en-us/magazine/2008.10.heyscriptingguy.aspx

There is no easy way and you have to program out everything you want.  If you don't need every bit of info, then don't feel like you need to extract it.  Also, for any data item you do need, make sure you test that there is a value or it will error if it is null or the node is not there.

Give it a try, play with your own code.  There will be trial and error to get it right.  Between what I have originally posted and the ms technet article, you should be able to get everything you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Graemewebber4technologiesAuthor Commented:
Found 1 nodes
HAROLDYOUNGMARIEYOUNGa Private Vehicle transferPrivate Vehicle: SIN Airport-City HotelsTOUR EAST SINGAPORE
1 Magazine RoadCentral Mall Office Tower#07-05/06 SINGAPORE 059567
0011 65 6735 1221HK84.00Only valid from Singapore Airport to City hotels.TERMINAL 1, 2 & 3:Upon leaving the arrival hall, please look for The Meet & Greetrepresentative who will be holding a TOUR EAST meeting boardIf for some reason a meeting does not take place, passengers can enquireon the whereabout of Tour East representative from:- The Airport Information Desk.- Terminal 1: Counter GTC 5, across from Belt 20-23- Terminal 2: Counter CTR 15, across from Belt 36-37- Terminal 3: Counter 6, across from Belt 41* Waiting time: 1 hour after flight lands. In the event you areheld up in the immigration, please contact Tour East Singapore.a Deluxe RoomDeluxe Room - Breakfast SingaporePARK HOTEL CLARKE QUAY
1 Unity StreetSingapore 237983
0011 65 6593 8833American BreakfastHK780.00a Private Vehicle transferPrivate Vehicle: City Hotels-SIN AirportTOUR EAST SINGAPORE
1 Magazine RoadCentral Mall Office Tower#07-05/06 SINGAPORE 059567
0011 65 6735 1221HK84.00Only valid from City hotels to Singapore AirportOWN ARRANGEMENTSa Private Vehicle transferPrivate Vehicle: SIN Airport-City HotelsTOUR EAST SINGAPORE
1 Magazine RoadCentral Mall Office Tower#07-05/06 SINGAPORE 059567
0011 65 6735 1221HK84.00Only valid from Singapore Airport to City hotels.TERMINAL 1, 2 & 3:Upon leaving the arrival hall, please look for The Meet & Greetrepresentative who will be holding a TOUR EAST meeting boardIf for some reason a meeting does not take place, passengers can enquireon the whereabout of Tour East representative from:- The Airport Information Desk.- Terminal 1: Counter GTC 5, across from Belt 20-23- Terminal 2: Counter CTR 15, across from Belt 36-37- Terminal 3: Counter 6, across from Belt 41* Waiting time: 1 hour after flight lands. In the event you areheld up in the immigration, please contact Tour East Singapore.an Upgrade to a Deluxe RoomUpgrade to Deluxe Room - Breakfast SINPARK HOTEL CLARKE QUAY
1 Unity StreetSingapore 237983
0011 65 6593 8833American BreakfastHK1604.00- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -UPGRADE OFFERStay a minimum of 4 consecutive nights and receive a complimentaryupgrade from a Superior Room to a Deluxe Room.Conditions:* Valid for stays between 20 June and 16 September 2015.* Not valid during Event periods and black-out dates.* Not combinable with other offers.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -UPGRADE OFFERStay a minimum of 4 consecutive nights and receive a complimentaryupgrade from a Superior Room to a Deluxe Room.Conditions:* Valid for stays between 20 June and 16 September 2015.* Not valid during Event periods and black-out dates.* Not combinable with other offers.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -UPGRADE OFFERStay a minimum of 4 consecutive nights and receive a complimentaryupgrade from a Superior Room to a Deluxe Room.Conditions:* Valid for stays between 20 June and 16 September 2015.* Not valid during Event periods and black-out dates.* Not combinable with other offers.a Private Vehicle transferPrivate Vehicle: City Hotels-SIN AirportTOUR EAST SINGAPORE
1 Magazine RoadCentral Mall Office Tower#07-05/06 SINGAPORE 059567
0011 65 6735 1221HK84.00Only valid from City hotels to Singapore AirportQuote createdBooking remarks for H/SIN/CPCQC on 10Sep to 15SepOption createdGRAEME MCGILVRAY authorised payment of 330.00
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Great you have it working.  View the source to see the actual xml.  

Between what I posted and the technet article, you should be able to figure out how to get data from individual nodes.  Like I said, it will take some time and trial and error.  Try going after one node of data instead of everything all at once because it will be easier to track one set of errors.
Graemewebber4technologiesAuthor Commented:
I will try to give it ago, still very forgein! lol

Im sure ill be back for more help
Graemewebber4technologiesAuthor Commented:
OK, trying to select the individual child nodes, not having any success to out to the XML

Basics:
<Identification Company="AE" BookingNo="604433"/>
<ReservationDetails BookingStatus="BOOKING"  DepartureDate="2015-09-01">

Can we start with these?

each 4 need to separate
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You have to give it a try. Look at the examples from technet and see if that gives you a clue. That example is very close to what you need.   If you run into troubles, post your code and error messages and I will check back in 12hours or so.  

The technet article uses
<?xml version='1.0'?> 
  <HARDWARE> 
      <COMPUTER os="Windows XP" department="Human Resources">atl-ws-001</COMPUTER> 
      <COMPUTER os="Windows XP" department="Finance">atl-ws-002</COMPUTER> 
      <COMPUTER os="Windows Server 2003" department="IT">atl-fs-003</COMPUTER> 
      <COMPUTER os="Windows Vista" department="IT">atl-ws-004</COMPUTER> 
      <COMPUTER os="Windows Vista" department="Human Resources">atl-ws-005</COMPUTER> 
      <COMPUTER os="Windows Vista" department="Finance">atl-ws-006</COMPUTER> 
      <COMPUTER os="Windows XP" department="Sales">atl-ws-007</COMPUTER> 
      <COMPUTER os="Windows Server 2008" department="IT">atl-fs-008</COMPUTER> 
      <COMPUTER os="Windows XP" department="Human Resources">atl-ws-009</COMPUTER> 
      <COMPUTER os="Windows Vista" department="Sales">atl-ws-010</COMPUTER> 
  </HARDWARE>

Open in new window

Note how they grab the node
    <COMPUTER os="Windows XP" department="Human Resources">atl-ws-001</COMPUTER> 

Open in new window

Set colNodes=xmlDoc.selectNodes _
  ("//HARDWARE/COMPUTER[@os='Windows XP']")

Open in new window

Graemewebber4technologiesAuthor Commented:
URL="http://www.activenquiry.info/bkgEnquiry.xml"

Set objXML=Server.CreateObject("MSXML2.DOMDocument")

objXML.setProperty "ServerHTTPRequest", True
objXML.async=false
objXML.Load(URL)

Set colNodes=objXML.selectNodes _
("//BookingInfo/Identification[@Company='AE']")
						
For Each objNode in colNodes
	Wscript.Echo objNode.Text 
Next

Open in new window


Microsoft VBScript runtime error '800a01a8'

Object required: ''

/playcode.asp, line 113 "Wscript.Echo objNode.Text"
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
For writing out to the screen you will use response.write and not wscript.echo
Graemewebber4technologiesAuthor Commented:
ok, fixed the response.write, however nothing shows on screen.

I would assume It would display the company AE ? would it not?

AE is the piece of information that I would require
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
fyi... the response.write will only get rid of the error, the output will be blank. The reason is in the technet sample there is text between the opening and closing tags and nothing else.  This xml is a bit different.  Plus what you are trying to get is the BookingNo.  

Notice in their output sample where they have
atl-ws-001
Human Resources

atl-ws-002
Finance

atl-ws-007
Sales

atl-ws-009
Human Resources

Open in new window


The way they get the 2nd line is
Wscript.Echo objNode.Attributes. _
      getNamedItem("department").Text

Open in new window

The underscore is just to make multiple lines.  You can get the same result by
Wscript.Echo objNode.Attributes.getNamedItem("department").Text

Open in new window

We also know that in Wscript.Echo is for vbscript at the command line.  For classic asp with vbscript that gets changed to response.write.  And let's replace that department with BookingNo  
response.write objNode.Attributes.getNamedItem("BookingNo").Text

Open in new window


You have a lot to go on here.
Graemewebber4technologiesAuthor Commented:
BookingNo is 1 of the items i would like, Company is another (as there are many companies that have the same XML layout to import)

i have adjusted the code accordingly and how shows those 2 i desire, the next 2 are:
ReservationDetails BookingStatus
ReservationDetails DepartureDate

Set colNodes=objXML.selectNodes _
	("//BookingInfo/Identification")
	("//BookingInfo/ReservationDetails")

For Each objNode in colNodes
	Response.Write objNode.Attributes.getNamedItem("Company").Text
	Response.Write objNode.Attributes.getNamedItem("BookingNo").Text
	Response.Write objNode.Attributes.getNamedItem("BookingStatus").Text
	Response.Write objNode.Attributes.getNamedItem("DepartureDate").Text
Next

Open in new window


Microsoft VBScript compilation error '800a0400'

Expected statement

/playcode.asp, line 111 ("//BookingInfo/ReservationDetails")
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Do one node at a time.

Notice that "//BookingInfo/Identification" relates to
<BookingInfo>
      <Identification>

Open in new window

And //BookingInfo/ReservationDetails relates to
<BookingInfo>
      <ReservationDetails>

Open in new window

Your code is combining those and that is why you are getting an error.   You have to work only one node at a time.
Set colNodes=objXML.selectNodes _
	("//BookingInfo/Identification")
	("//BookingInfo/ReservationDetails")

Open in new window

Graemewebber4technologiesAuthor Commented:
Thanks, got the data working

Now to the next bit...

INSERTING into a table

I would assume the easiest way would be:

Supplier=objNode.Attributes.getNamedItem("Company").Text
BookingRef=objNode.Attributes.getNamedItem("BookingNo").Text
Status=objNode.Attributes.getNamedItem("BookingStatus").Text
DepDate=CDate(objNode.Attributes.getNamedItem("DepartureDate").Text)

If Status="Quote" Then
	SupplierStatus=1
ElseIf Status="BookingStatus" Then
	SupplierStatus=2
End If
Set Supplier=oConn.Execute("SELECT * FROM suppliers WHERE supplier_short='"&SupplierShort&"'")

oConn.EXECUTE(INSERT INTO enq_land(cons_ID,cons_pseudo,enq_ID,land_enq_date,land_findate,land_supplier,land_process,supplier_ref) VALUES("&Session("ID")&",'"&NameShop("cons.office_pseudo")&"',"&Request.Form("enqid")&",'"&date()&"','"&DepDate&"',"&Supplier("supplier_ID")&","&SupplierStatus&",'"&BookingRef&"')")

Open in new window


simplify them? then execute the INSERT?

I have put this into practice, however no inserting is happening, I have also tried to Response.Write the oConn without luck as well.
Also, yes BookingNo is going into a Text feild
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
There are 3 parts to your question.

1) Uploading the xml file (or is it generated via an api or some type of get or post?)

2) Parsing the data from the xml

3) Saving the data to your database.

What we worked on in this question is #2 parsing the data and if you have that working, then you should close out this question and ask separate questions for the other 2 parts to keep this organized.

Uploading is going to be done by some type of 3rd party component like aspupload http://www.aspupload.com/.  Choose the file location where the xml will go, make sure that file location has write permissions and use the component to upload.  This is the same way you would upload an image or text file.  

For saving data to your db, there is an article on using parameterized insert query that will get you started.  http://www.experts-exchange.com/articles/3626/ASP-Classic-Using-Parameterized-Queries.html

Essentially your sql is "INSERT into MyTable(Field1, Field2, Field3)values('something1','something2','something3')" but you want to use Parameterized insert to keep you safe.

Since each of these 3 items come with their own gotchas, it will be easier to break this question out to these 3 parts.
Graemewebber4technologiesAuthor Commented:
Cheers for that, I will start the other 2 Questions :)
Graemewebber4technologiesAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.