Insert, Update SQL Server table data from a API Url

I'm not sure if SQL Server or can do this since I'm not an expert. I'm using an API from a e-commerce site. They have a way of using a url to download an xml file of any parameters i choose from a particular table.

Does SQL Server have a capability to insert and update a table from a URL link?

I guess my other alternative would be to use a VBscript to auto download the xml file to my hard drive but not sure how to auto trigger the xml file to the database table. Would like to auto trigger vbscript to get data and insert- update SQL tables every 15 minutes.

Any suggestions or resources I can go to learn how to do this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL Server Information Services (SSIS) has a Web Services task, which calls a URL method, which returns an XML file.  Then you can use that file either as a source of a data flow, or via T-SQL, to pump it into any destination you wish.

I don't have any experience in using T-SQL to actually make an API / web service call, so I'll not answer that part.

Hope this helps.
Let me see if I correctly understand it...  You have a local SQL server.  You also have the ability to pull some data from a URL in XML format.  You want to be able to grab that data, and insert it into a database on your local server.  Is that correct?

SQL Server cannot do it on it's own, but you can create a .NET service, or a scheduled app, or a SQL Job that executes a CLR function, and use the .NET code to load an XML document from StreamReader, and then work with the document node by node, retrieve data, and then insert or update your database.

YOu can also bulk-import XML into SQL server, but that is more for static XML files.  Here is a code sample for you:
            Page.Response.ContentType = "text/xml"
            Dim reader As New System.IO.StreamReader(Page.Request.InputStream)
            strXMLData = reader.ReadToEnd()
            Dim xmlDoc As New System.Xml.XmlDocument
            Dim xmlRequestElement As XmlElement, xmlDetailElement As XmlElement
            Dim intCashId As Integer, datDateTime As Date, intChequeNum As Integer, strCardNum As String, intDocNum As Integer, decBonus As Double, decTotal As Double
            strCardNum = ""

            For Each requestNode In xmlDoc.DocumentElement.ChildNodes
                xmlRequestElement = requestNode
                If xmlRequestElement.Name = "request" Then
                    For Each detailNode In xmlRequestElement.ChildNodes
                        xmlDetailElement = detailNode
                        Select Case xmlDetailElement.Name
                            Case "cash_id"
                                intCashId = CInt(xmlDetailElement.InnerText.ToString)
                            Case "datetime"
                                Dim strD As String = xmlDetailElement.InnerText.ToString
                                datDateTime = CDate(strD.Substring(4, 4) & "-" & strD.Substring(2, 2) & "-" & strD.Substring(0, 2) & " " & strD.Substring(8, 2) & ":" & strD.Substring(10, 2) & ":00.000")
                            Case "cheque_num"
                                intChequeNum = CInt(xmlDetailElement.InnerText.ToString)
                            Case "doc_num"
                                intDocNum = CInt(xmlDetailElement.InnerText.ToString)
                            Case "customer"
                                strCardNum = xmlDetailElement.InnerText.ToString
                            Case "sum_bonus"
                                decBonus = CDbl(xmlDetailElement.InnerText.ToString.Replace(",", "").Replace(".", ","))
                            Case "sum_total"
                                decTotal = CDbl(xmlDetailElement.InnerText.ToString.Replace(",", "").Replace(".", ","))
                            Case Else
                        End Select

                End If

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.