Import xml http feed into sql or onto local machine

[emmetmurray]

Hi,

Can anyone tell me how to download the data from a xml http feed? Preferably to csv,text, xml file.

I need to get the info from my http xml feed into a sql server database. i have created a mapping scheme for a xml files on my local machine and it works fine, but i need to be able to somehow get the info from my http xml feed into the database, or at least onto a local machine, and i can then run my script.

This is what i've been using so far, but i need to replace "products.xml" with a http url

http://www.somesite.com/products.xml

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=IP;database=products;uid=sa;pwd=somepassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\xml\productmapping.xml", "c:\xml\products.xml"
Set objBL = Nothing

Any advice would be great.

Thanks

 

[tsuji]

The bare-bone structure is like this, no error control, no nothing else.
[tt]
'input setting
surl="[ignore]

http://www.somesite.com/products.xml

[/ignore]"
xmlfile="c:\xml\products.xml"

set oxmlhttp=createobject("msxml2.xmlhttp")
with oxmlhttp
.open "GET",surl,false
.send
end with
oxmlhttp.responsexml.save xmlfile
'if you want it in the memory to further processing in-memory, reference it with oparser below
'set oparser=oxmlhttp.responsexml
set oxmlhttp=nothing[/tt]

 

[emmetmurray]

Thats absolutely brilliant. Worked great.

I download the xml, then i run a vbscript using sqlbulkload, to import the downloaded xml into sql.

Just wondering now, is there a way to check if a record already exists, as i may want to run this periodically to update the database?


VBscript

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=10.2.16.38;database=test_products;uid=sa;pwd=test"
objBL.ErrorLogFile = "c:\xml\error.log"
objBL.Execute "c:\xml\productmapping.xml", "c:\xml\products.xml"

MsgBox "XML imported to sql server!", "0", "XML imported to sql server"

Set objBL = Nothing

xml mapping schema

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="ID" dt:type="int" />
<ElementType name="event_token" dt:type="char" />
<ElementType name="event_desc" dt:type="nvarchar" />

<ElementType name="event_list" sql:is-constant="1">
<element type="event" />
</ElementType>

<ElementType name="event" sql:relation="products">
<element type="event_token" sql:field="event_token" />
<element type="event_desc" sql:field="event_desc" />
</ElementType>

</Schema>

Thanks

 

[rhiannonamybuck]

I really need to know the answer to this too - at the moment it just adds the same data to the database again and again.

Did you work out how to get it to check whether the record exists before puting it in?

I'm having a nightmare! Here is my code:

-------------------------------------------------------

GET XML:

Const ForWriting = 2
strURL="

http://CLIENT-URL-HERE"

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
Call objHTTP.Open("GET", strURL, FALSE)
objHTTP.Send
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("films.xml", ForWriting)
objFile.Write objHTTP.ResponseText
objFile.Close



POP IT IN MY DATABASE:

Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
oBL.ConnectionString = "MY-CONNECTION-STRING"
oBL.KeepIdentity = False
oBL.ErrorLogFile = "error.log"
oBL.Execute "iden.xsd", "films.xml"