• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

Insert, Update SQL Server table data from a API Url

I'm not sure if SQL Server or ASP.net 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?
2 Solutions
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now