Dtqstdreqhkgj
New Member
First off I would like to say that I think that my question may be time consuming for people to solve it in a complete sense so I understand that it is totally possible that the complete answer is asking for just way too much, so anything to help me better understand, like: reading material, examples, links, and/or advice would be great and I do very much appreciate any and every comment I receive, good or bad it just makes me and this place alot better, finally, I would like to thank you all so much for everything that you do here, this is truly a place that was build by smart people, and people that care.MY QUESTION(using Classic ASP and SQL Server)I know that it is possible to read a remote XML file and then insert it into a SQL Server database table. I found one example that does that it uses Classic ASP and MS Access but that can be changed to SQL Server with minimal coding the URL is: http://forums.aspfree.com/code-bank-54/classic-asp-import-remote-xml-file-into-database-152966.htmlBut the problem is that I cannot get it to work on my remote XML file, I tried to edit the classic asp code (found in the link above) for days and days and I just cannot get it to work the way I would like.So I want to start from the beginning,The XML file in question is located on: http://www.iftach.org/taxmatrix/charts/2Q2012.xmlI seen a couple of examples on how you can export the entire xml file into the SQL Server database (like do a BULK insert, see URL: http://support.microsoft.com/kb/316005) and also on how to extract some info. from the XML but my request is kind of odd since I want to check for the \[code\]Country\[/code\] first and then get that \[code\]Counties Rate\[/code\] only and not the other one, I want to do this for the entire xml file.For example the xml file is something like this:
(or you can view the full xml file by clicking on the URL above)\[code\]<FILE><QUARTER>2Q2012</QUARTER><RECORD><JURISDICTION ID="#15">AB</JURISDICTION><COUNTRY>CAN</COUNTRY>............<RATE RATECHANGE="0" COUNTRY="US">0.3366</RATE><RATE RATECHANGE="0" COUNTRY="CAN">0.0900</RATE>..................</RECORD><RECORD><JURISDICTION ID="#15">FL</JURISDICTION><COUNTRY>U.S.</COUNTRY>............<RATE RATECHANGE="0" COUNTRY="US">1.5700</RATE><RATE RATECHANGE="0" COUNTRY="CAN">1.3210</RATE>..................</RECORD></FILE>\[/code\]and so on....Now I would like to insert that info into the SQL Server table called \[code\]FFTR\[/code\] and name the column specific for each \[code\]JURISDICTION\[/code\] Like for example the above would be: \[code\]Field Name 1 --> "JURISDICTION_AB_CAN"Field Name 2 --> "JURISDICTION_FL_US"and so on...\[/code\]NOTE:
The prefix \[code\]JURISDICTION_\[/code\] will always be the same only the two letters will change and the \[code\]CAN\[/code\] can become \[code\]US\[/code\].Another thing is if the COUNTRY is "CAN" then I would like to use the CAN Rate and if it's U.S. I would like to use the US Rate and insert that info. into the database with the Field named "RATE". (The Rate will always be 4 decimal places) the Rate I want is only under: \[code\]<FUEL_TYPE>Special Diesel</FUEL_TYPE>\[/code\] I don't need the other Rates.And the last thing I would like to do is to have the \[code\]<QUARTER>2Q2012</QUARTER>\[/code\] inserted into a Field named "Quarter"So the final SQL Server database would look like this (using the 2 records as an example above)\[code\]Field Name: JURISDICTION_AB_CANRate: 0.0900Quarter: 2Q2012Field Name: JURISDICTION_FL_USRate: 1.5700Quarter: 2Q2012\[/code\]So what I tried to do is this (see code below) and I got it to show each line but it doesn't even come close to a solution:\[code\]<% Option Explicit Response.Buffer = True Dim xml Set xml = Server.CreateObject("Microsoft.XMLDOM") xml.async = False xml.setProperty "ServerHTTPRequest", True xml.Load ("http://www.iftach.org/TaxMatrix/charts/2Q2012.xml") Dim paragraph1,paragraph2,paragraph3,paragraph4,paragraph5,paragraph6 paragraph1 = xml.documentElement.childNodes(1).text paragraph2 = xml.documentElement.childNodes(2).text paragraph3 = xml.documentElement.childNodes(3).text paragraph4 = xml.documentElement.childNodes(4).text paragraph5 = xml.documentElement.childNodes(5).text paragraph6 = xml.documentElement.childNodes(6).text Set xml = Nothing %> <html> <head> <title></title> </head> <body> <p align="center"><% = paragraph1 %></p> <p align="center"><% = paragraph2 %></p> <p align="center"><% = paragraph3 %></p> <p align="center"><% = paragraph4 %></p> <p align="center"><% = paragraph5 %></p> <p align="center"><% = paragraph6 %></p></body></html> \[/code\]I even think that adding it to a ADODB Recordset would be great and then I would insert it into SQL Server one by one or just loop it all in there, but it only shows me the columns I need the rows in there also. See code below:\[code\]<% Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;" objRS.Open(Server.MapPath("2Q2012.xml"))Response.Write(objRS.Fields(2) & "<br>") ' <-- Returns the Quarter only, that I need for the Quarter Field in the DB'Response.Write(objRS.Fields(6) & "<br>") ' <-- Returns the entire xml pageDo While Not objRS.EOF objRS.MoveNext Loop%><table border="1" width="100%"> <% dim fld Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Name & "</td>") End If Next Response.Write("</tr>") Do While Not objRS.EOF Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Value & "</td>") End If Next Response.Write("</tr>") objRS.MoveNext Loop %> </table>\[/code\]Again, Thank you so much for any advice, links, or any help at all...
(or you can view the full xml file by clicking on the URL above)\[code\]<FILE><QUARTER>2Q2012</QUARTER><RECORD><JURISDICTION ID="#15">AB</JURISDICTION><COUNTRY>CAN</COUNTRY>............<RATE RATECHANGE="0" COUNTRY="US">0.3366</RATE><RATE RATECHANGE="0" COUNTRY="CAN">0.0900</RATE>..................</RECORD><RECORD><JURISDICTION ID="#15">FL</JURISDICTION><COUNTRY>U.S.</COUNTRY>............<RATE RATECHANGE="0" COUNTRY="US">1.5700</RATE><RATE RATECHANGE="0" COUNTRY="CAN">1.3210</RATE>..................</RECORD></FILE>\[/code\]and so on....Now I would like to insert that info into the SQL Server table called \[code\]FFTR\[/code\] and name the column specific for each \[code\]JURISDICTION\[/code\] Like for example the above would be: \[code\]Field Name 1 --> "JURISDICTION_AB_CAN"Field Name 2 --> "JURISDICTION_FL_US"and so on...\[/code\]NOTE:
The prefix \[code\]JURISDICTION_\[/code\] will always be the same only the two letters will change and the \[code\]CAN\[/code\] can become \[code\]US\[/code\].Another thing is if the COUNTRY is "CAN" then I would like to use the CAN Rate and if it's U.S. I would like to use the US Rate and insert that info. into the database with the Field named "RATE". (The Rate will always be 4 decimal places) the Rate I want is only under: \[code\]<FUEL_TYPE>Special Diesel</FUEL_TYPE>\[/code\] I don't need the other Rates.And the last thing I would like to do is to have the \[code\]<QUARTER>2Q2012</QUARTER>\[/code\] inserted into a Field named "Quarter"So the final SQL Server database would look like this (using the 2 records as an example above)\[code\]Field Name: JURISDICTION_AB_CANRate: 0.0900Quarter: 2Q2012Field Name: JURISDICTION_FL_USRate: 1.5700Quarter: 2Q2012\[/code\]So what I tried to do is this (see code below) and I got it to show each line but it doesn't even come close to a solution:\[code\]<% Option Explicit Response.Buffer = True Dim xml Set xml = Server.CreateObject("Microsoft.XMLDOM") xml.async = False xml.setProperty "ServerHTTPRequest", True xml.Load ("http://www.iftach.org/TaxMatrix/charts/2Q2012.xml") Dim paragraph1,paragraph2,paragraph3,paragraph4,paragraph5,paragraph6 paragraph1 = xml.documentElement.childNodes(1).text paragraph2 = xml.documentElement.childNodes(2).text paragraph3 = xml.documentElement.childNodes(3).text paragraph4 = xml.documentElement.childNodes(4).text paragraph5 = xml.documentElement.childNodes(5).text paragraph6 = xml.documentElement.childNodes(6).text Set xml = Nothing %> <html> <head> <title></title> </head> <body> <p align="center"><% = paragraph1 %></p> <p align="center"><% = paragraph2 %></p> <p align="center"><% = paragraph3 %></p> <p align="center"><% = paragraph4 %></p> <p align="center"><% = paragraph5 %></p> <p align="center"><% = paragraph6 %></p></body></html> \[/code\]I even think that adding it to a ADODB Recordset would be great and then I would insert it into SQL Server one by one or just loop it all in there, but it only shows me the columns I need the rows in there also. See code below:\[code\]<% Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;" objRS.Open(Server.MapPath("2Q2012.xml"))Response.Write(objRS.Fields(2) & "<br>") ' <-- Returns the Quarter only, that I need for the Quarter Field in the DB'Response.Write(objRS.Fields(6) & "<br>") ' <-- Returns the entire xml pageDo While Not objRS.EOF objRS.MoveNext Loop%><table border="1" width="100%"> <% dim fld Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Name & "</td>") End If Next Response.Write("</tr>") Do While Not objRS.EOF Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Value & "</td>") End If Next Response.Write("</tr>") objRS.MoveNext Loop %> </table>\[/code\]Again, Thank you so much for any advice, links, or any help at all...