url xml data to mssql database using ASP

reycali · March 11th, 2009, 06:41 AM

Hi there,

Could you please help on how to insert a xml data from url

http://www.alineah.com/customers.xml

output

- <ROOT>
- <Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
- <Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
- <Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

and insert this data to my mssql database...

my code

<%
url="http://www.alineah.com/customers.xml"
set xml = Server.CreateObject("MSXML2.DOMDocument")
xml.async = false
xml.setProperty "ServerHTTPRequest", true
xml.load(url)

'Load XSL
set xsl = Server.CreateObject("MSXML2.DOMDocument")
xsl.async = false
xsl.load(Server.MapPath("xstyle/prov.xsl"))
'Transform file
Response.Write(xml.transformNode(xsl))
%>

xsl code

<?xml version='1.0' encoding='utf-8' ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:include href="prov_css.xsl"/>
<xsl:template match="/">
<xsl:call-template name="style1" />
<div>
<xsl:for-each select="ROOT">
<xsl:for-each select="Customers">
<div class="label001"><xsl:value-of select="CompanyName"/></div><BR />
</xsl:for-each>
</xsl:for-each>
</div>
</xsl:template>
</xsl:stylesheet>

Old Pedant · March 12th, 2009, 12:48 AM

I just happened to do essentially the same thing with an Access DB, so the changes will be minor. I'll just post my code. Note: The XSL file is irrelevant to this, unless you are doing something stranger than what I think you are doing.

My code:

Code:

<%
' I'll let you get the xmlsrc as a string...

Set oxml = Server.CreateObject("msxml2.domdocument")
okay = oxml.LoadXML( xmlsrc )
If Not okay Then
    Response.Write "Unable to load XML document"
    Response.End
End If

Set orders = oxml.getElementsByTagName("order")
For Each order In orders
    Set info = order.SelectSingleNode("userinfo")
    name    = Replace( info.SelectSingleNode("name").text, "'", "''" )
    address = Replace( info.SelectSingleNode("address").text, "'", "''" )
    Set zipnode = info.SelectSingleNode("zipcode")
    If zipnode Is Nothing Then 
        zip = "NULL"
    Else 
        zip = CLng(zipnode.text) ' must be a number only in this simple example
    End If
    SQL = "INSERT INTO orders (name,address,zipcode) " _
        & "VALUES('" & name & "','" & address & "'," & zip & ")"
    Response.Write "<P>" & SQL & "<br/>" & vbNewLine
    conn.execute SQL
    SQL = "SELECT @@IDENTITY"
    Set RS = conn.execute( SQL )
    orderid = RS(0)
    RS.Close

    Set details = order.getElementsByTagName("orderdetails")
    For Each detail In details
        id = Replace( detail.SelectSingleNode("orderdetailID").text, "'", "''" )
        item = Replace( detail.SelectSingleNode("orderitem").text, "'", "''" )
        SQL = "INSERT INTO orderDetails (orderid, detailid, detailtext ) " _
            & " VALUES(" & orderid & "," & id & ",'" & item & "')"
        Response.Write SQL & "<br/>" & vbNewLine
        conn.Execute SQL
    Next         
Next
%>

You can see that my code is expecting an ORDER, which would have master info for an order and then a set of orderitems. Since you don't have any "master" info, you would only need the loop.

If you can't get it from that, I'll try to custom write for you.

[/code]

AlanJudin · July 28th, 2016, 03:59 AM

Exporting xml data from web browser (correct in page source) to Sql

Hi, I noticed from previous posts that you have looked at things like this before, my appreciation in advance for any help I can get.

I am consuming a webservice in a classic asp page.
http://sams.avusa.co.za/NewSales/WebServices/test1.asp

I manage to get the xml response available to the browser.

But cannot get the oXmlHTTP.responseText to write to the field in the database.database.

Code:

 
<!-- #INCLUDE file="connections.inc" -->
<%
		Set oXmlHTTP = CreateObject("Microsoft.XMLHTTP")
		oXmlHTTP.Open "POST","http://172.15.83.33:28080/webabo2_jcd/WebAbo?", False 
		oXmlHTTP.setRequestHeader "Content-Type", "text/xml; charset=utf-8" 
		oXmlHTTP.setRequestHeader "SOAPAction", "http://172.15.83.33:28080/webabo2_jcd/WebAbo"
		
SOAPRequest = "<?xml version=""1.0"" encoding=""utf-8""?><soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:ser=""http://hup.de/WebAbo/Service""><soapenv:Header/><soapenv:Body><ser:getOfferList><titlesToLoad/></ser:getOfferList>/soapenv:Body></soapenv:Envelope>"
		
		oXmlHTTP.send SOAPRequest  
		Response.Write oXmlHTTP.responseText
		
		
sql1 = "SELECT * FROM HIF_WS_XML"
set loadXML= server.Createobject("ADODB.Recordset")
loadXML.open sql1, conn, 1, 3
loadXML.AddNew
loadXML("XMLDATA") = oXmlHTTP.responseText	
loadXML.update

response.write sql1
response.end
%>