经典的ASP将远程XML文件导入SQL Server

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)

(使用经典的ASP和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.html

我知道可以读取远程XML文件，然后将其插入到SQL Server数据库表中。我发现了一个例子，它使用了经典的ASP和MS访问，但是可以将其更改为SQL Server，只需对URL进行最少的编码:http://forums.aspfree.com/code-bank-54/classic-asp-import-remote-xml-file-into-database-152966.html

But 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.

但是问题是我不能让它在我的远程XML文件上工作，我试着编辑经典的asp代码(在上面的链接中可以找到)，我只是不能让它以我想要的方式工作。

So I want to start from the beginning,

我想从一开始，

The XML file in question is located on: http://www.iftach.org/taxmatrix/charts/2Q2012.xml

该XML文件位于:http://www.iftach.org/taxmatrix/charts/2Q2012.xml

I 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 Country first and then get that Counties Rate only and not the other one, I want to do this for the entire xml file.

我看到了几个示例，说明如何将整个xml文件导出到SQL Server数据库(如批量插入，参见URL: http://support.microsoft.com/kb/316005)，以及如何提取一些信息。但是我的请求有点奇怪，因为我想先检查国家，然后得到县的利率，而不是另一个，我想对整个XML文件这样做。

For example the xml file is something like this:
(or you can view the full xml file by clicking on the URL above)

例如，xml文件是这样的:(或者您可以通过单击上面的URL查看完整的xml文件)

<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>

and so on....

等等....

Now I would like to insert that info into the SQL Server table called FFTR and name the column specific for each JURISDICTION

现在，我想将该信息插入到名为FFTR的SQL Server表中，并为每个权限指定列

Like for example the above would be:

例如，上面将是:

Field Name 1 --> "JURISDICTION_AB_CAN"
Field Name 2 --> "JURISDICTION_FL_US"
and so on...

NOTE:
The prefix JURISDICTION_ will always be the same only the two letters will change and the CAN can become US.

注意:前缀的管辖权将永远是相同的，只有两个字母会改变，而CAN可以变成我们。

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: <FUEL_TYPE>Special Diesel</FUEL_TYPE> I don't need the other Rates.

另一件事是如果这个国家是“CAN”那么我想用CAN Rate如果是美国我想用US Rate插入那个信息。使用名为“RATE”的字段进入数据库。(速率总是小数点后4位)我想要的速率是: Special Diesel 我不需要其他的速率。

And the last thing I would like to do is to have the <QUARTER>2Q2012</QUARTER> inserted into a Field named "Quarter"

最后我要做的是将 2Q2012 插入到一个名为" QUARTER "的字段中

So the final SQL Server database would look like this (using the 2 records as an example above)

因此，最终的SQL Server数据库将是这样的(使用上面的两个记录作为示例)

Field Name: JURISDICTION_AB_CAN
Rate: 0.0900
Quarter: 2Q2012

Field Name: JURISDICTION_FL_US
Rate: 1.5700
Quarter: 2Q2012

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:

所以我试着做的是(见下面的代码)我让它显示每一行但它甚至没有接近一个解决方案:

<% 
   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> 

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:

我甚至认为将它添加到ADODB记录集是很好的，然后我将它逐个插入到SQL Server中或者只是在其中循环，但它只显示了我需要的列以及其中的行。请参见下面的代码:

<%
 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 page

Do 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>

Again, Thank you so much for any advice, links, or any help at all...

再次感谢您提供的任何建议、链接或任何帮助……

2 个解决方案

#1

have a look here: msxml DOM

看看这里:msxml DOM

you should use the msxml object to read the xml. then you can query the elements of the xml by using the api.

您应该使用msxml对象来读取xml。然后可以使用api查询xml的元素。

example code for loading the xml:

加载xml的示例代码:

<%
dim xml : set xml = server.createobject("Msxml2.DOMDocument.6.0")
dim xmlString : xmlString = getXMLHTTPResponse("http://www.iftach.org/TaxMatrix/charts/2Q2012.xml")
xml.loadxml(xmlString)

function getXMLHTTPResponse(url)
    dim tout, xmlhttp

    set xmlhttp = server.createObject("Msxml2.ServerXMLHTTP.6.0")
    with xmlhttp
        .setTimeouts 2000, 2000, 2000, 2000
        .open "GET", url, false
        .send()
        if .responseXML.xml = "" then
            getXMLHTTPResponse = .responseText
        else
            getXMLHTTPResponse = .responseXML.xml
        end if
    end with
end function
%>

#2

Finally!!! I made it work, if anyone needs the solution it is below: Using the code I posted in the question I added the following code:

最后! ! !我使它工作，如果有人需要解决它是以下:使用我在问题中张贴的代码我添加了以下代码:

Note: I know that this is not the best way to do it since I'm not going after the TAGS/IDs/Names but the xml file will always stay formated the same so I just Looped, Cut out what I needed, and Inserted/Updated into the DB.

注意:我知道这不是最好的方法，因为我不追求标记/ id /名称，但是xml文件将始终保持格式不变，所以我只是循环，裁剪需要的内容，然后插入/更新到DB中。

    <%
    ' LOOP ALL OF THE FIELDS ONE BY ONE
     For x = 0 to xml.documentelement.childnodes.length - 1
     set XMLobjchildnodes = xml.documentelement.childnodes
     strXMLtxt=xml.documentElement.childNodes(x).text & "<br>"


    ' SETUP THE UPDATE FIELDS FOR SQL
     dim strTest
     strTest="objSQL(""IMP_STATE_" & Mid(strXMLtxt,1,2) & """)=" & Mid(strXMLtxt,46,7)

    ' SKIP THE Fi and 2Q because they are not States/Prov.
    if strTest="objSQL(""IMP_STATE_Fi"")=" OR strTest="objSQL(""IMP_STATE_2Q"")=" then
    else

    ' ALSO SKIP hi and U and CN because they also are not States/Prov.
    if InStr(strTest,"STATE_ht")>0 OR InStr(strTest,"STATE_U.")>0 OR InStr(strTest,"STATE_CN")>0 then
    else

    ' ADD YOUR SQL CONNECTION INFO. HERE AND THEN INSERT/UPDATE THE SQL DB


    end if
Next
    %>

Note: By making small changes you can set it to Insert the data into SQL and/or update, should this be a problem for anyone please let me know I will be more then happy to help.

注意:通过做小的更改，您可以设置它将数据插入到SQL和/或更新中，如果这是一个问题，请告诉我，我将更乐意帮助。

To all that have tried to solve my problem/question, Thank you so much for all of your hard work and effort.

对于所有试图解决我问题的人，非常感谢你们的辛勤工作和努力。