Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP XML
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP XML Using ASP 3 and XML. See also the XML category for more XML discussions not relating to ASP. NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP XML section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 11th, 2009, 06:41 AM
Registered User
 
Join Date: Mar 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default url xml data to mssql database using ASP

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>
Reply With Quote
  #2 (permalink)  
Old March 12th, 2009, 12:48 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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]
Reply With Quote
  #3 (permalink)  
Old July 28th, 2016, 03:59 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Red face Exporting xml data from web browser (correct in page source) to Sql

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
%>
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MSSQL 2005 and MSSQL 2000 tiredcat Visual Basic 2005 Basics 0 April 9th, 2007 12:56 AM
uploading mssql database vickyj SQL Server 2000 1 May 20th, 2005 08:11 AM
ASP / ADO / MSSQL Problem webdev Classic ASP Databases 0 December 8th, 2004 01:21 PM
VB MSSQL database REmote connectivity srichary Pro VB Databases 0 November 28th, 2004 05:33 AM
Crucial - need help with ras + asp + mssql sprocs cr_ras_zke Crystal Reports 0 October 18th, 2003 08:02 PM



All times are GMT -4. The time now is 08:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.