May 24, 2012 at 12:28 pm
Hello there. I've been administering our company's SQL database for a good while now, and I've begun to move into an idea I had to use XML to handle API calls that rely on our data. Unfortunately, I have essentially no XML experience, so I'd like a few pointers on how to handle XML in the context of SQL Server.
Looking at the MSDN article on SQLXML, it seems that it should be possible to generate an XML document by using FOR XML and formatting as needed, with the additional ability to insert parameters into the XML generation statements that would hold data pulled from our database; from there, it should be possible to export the contents to a file, correct? At this point, though, I'm a bit lost. I need to get the XML document sent out and retrieve a response, and I'm not sure this is something SQL Server is capable of (and I honestly wouldn't expect it, following the logic that the prime function of a database should be to house data, rather than firing off calls across the web).
If my reasoning holds true, how would I get the SQL-generated XML documents out to the API in question? I believe the POST command for XML should do it, but I honestly don't have a clue how or where I'd direct that to where it should go. That's my biggest stumbling block at the moment.
If this is too much of an XML-based question, I understand; I'll toss this thread out and try the XML section of the forums instead. I would be pleasantly surprised if my guess about SQL's XML transmission is wrong, but I'll adapt as needed.
Many thanks for your time.
- 😀
May 24, 2012 at 4:56 pm
I am not sure that we can help you very much without more details or a more concrete example/question.
I can say however that in general, SQL Server does not push to clients, rather the clients must pull from SQL Server, by issuing requests (in the form of queries), which it will respond to.
This is somewhat different, however, if you wanted to push to another DB server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2012 at 1:32 am
As far as I can see, this can be done in several ways. If the target for your data also is a SQL Server, then Service Broker is a good option (although it can be a bit tough to troubleshoot at times). If you need a way of calling a web service, this can be done with SSIS, or you could even create your own .NET CLR Procedure doing it for you.
May 25, 2012 at 7:48 am
Barry, my apologies for not providing more details. I meant to have a more general question, since I feel this is more of an XML issue than a SQL Server one, but here's the complete situation:
Currently, we'd like to be able to calculate shipping rates through the UPS and USPS APIs. We'd like to be able to feed a customer's shipping information to the API and get the pricing for every form of shipping we usually use to mail a package to that customer. Both APIs accept XML as input; as such, I've tinkered with SQL's XML capabilities, and I've been successful at creating an XML document that matches the format of the ones we usually use in the office.
Background: We currently have a program that does exactly what we want, in theory; however, it's not documented at all, and the variable names are extremely vague (to this level: object b has properties a, b, c, d, and e), plus we'd like more functionality, hence the need for this project.
Although I can create the XML document that would need to get to the APIs successfully, I have no idea how to actually get that XML to the API, thus my question; I was wondering if it would be possible to have SQL Server get the XML to the APIs on its own strength, which I doubted was possible; your answer seems to reinforce that belief.
Ole, I hadn't even considered SSIS, which was definitely a mistake; really, it probably should've been the first place I looked. My backup plan was to use PowerShell if possible, but I think SSIS could be a much more usable solution. Thank you very much for the link; I'll start researching right away.
Thank you both for your time; I feel like I know where I'm headed now 😀
- 😀
May 25, 2012 at 10:26 am
As okbangas said, SSIS is definitely one way to go, as is Service Broker + SQLCLR. If you find SQLCLR unreasonably constricting (I do) for this kind of task, you could also try using the Service Broker External Activator (see here), which I find much preferrable for this kind of background/webservice resolution because it makes the safety/security issues much easier to deal with.
If, on the other hand, you need interactive/real-time synchronous request resolution, then I think that SQLCLR is about the only way to do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2012 at 11:32 am
Barry, thank you for the additional information. Service Broker may be a viable solution to this problem as well. After doing some more research, it seems like the best fit for the problem with our current skillset would be as such, with potential modifications based on what I'm learning along the way:
1. Generate XML document in SQL Server using FOR XML queries with variables to insert address data
2. Export document from SQL Server
3. Write a process in either SSIS or C# to POST the document across HTTP to the APIs (If using SSIS, these three steps could probably all be folded together)
4. Get response from API and write it to another XML document
5. Read the response document back into SQL Server into a staging table to do comparisons between shipping costs, pick best cost, UPDATE order information with what we've discerned to be the best shipping method
Does this seem like a viable plan? From my own examinations, it seems like this would essentially be what we're doing now, but it would be much more responsive; I was thinking that the XML document generation and processing could be done between a combination of a SQL job for generation, and a Task Scheduler job to fire off the C# program for regular order-checking. I may be missing something in my analysis, though.
Thanks again for all the help, it's greatly appreciated.
- 😀
May 26, 2012 at 12:53 pm
The questions that make the biggest difference in which way you go are probably:
1) What the current available skillset is there, and
2) What kind of responsiveness you are actually looking for
If you can tell us those two things, then we can better advise you on which direction would likely work best for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2012 at 5:29 pm
Certainly, Barry.
1. Currently, the available skillset at this business consists of just me :hehe:. I'd say I'm quite good with SQL Server at this point, and where I'm lacking with knowledge in the field, I try my best to learn as fast as possible. That aside, my skills in other languages are fairly shaky. I know a good bit of PowerShell, which is how I've cobbled some solutions together that were beyond SQL Server alone, and a small bit of C#, which is mostly from looking at the other application the previous programmer left our business.
2. The responsiveness of the solution doesn't have to be particularly fast; in essence, we bring in our orders for the day early in the morning, then the shipping recommendations need to be checked about two hours later. I was thinking of checking for whether recommendations are present every 15 minutes, then running the procedure to generate shipping recommendations if the field for them is found to be NULL (which would be easy enough, since the recommendation field is NULL when it's brought in, then it's filled with the correct recommendation after our current process runs). About the only requirement would be that I'd like it if the process could run automatically as mentioned previously.
Thanks again for the help; I'm doing a bit of research here and there on the long weekend, and I'll be back to tinkering away at the office on Tuesday to test some ideas.
- 😀
May 26, 2012 at 6:15 pm
Do you/your company already have something that sends XML to the API and/or receives XML docs back from it? Not necessarily SQL Server, just something that already works that you can either repurpose for your own files, etc. or else use as a working source for code cut-and-paste.
I ask because that seems to me to be the murkiest area here. Everything else is pretty SOP and has one or more fairly easy ways to do it from SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2012 at 6:17 pm
{ deleted: duplicate post ...}
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2012 at 7:19 pm
Barry, we do indeed have a current tool to send XML from our system to the shipping APIs; however, the problems with it are numerous. First, it was composed with no documentation at all, so extending its uses (which is most of what I'll need to do with this project) is very difficult; this is compounded further by rather terrible naming conventions. For example, a box is of the class "b". Its property "a" corresponds to a zip code, property "b" corresponds to the city, and so on. Most of it is decipherable with some thorough scrutiny, but the main problem with it is that I can't seem to figure out how it composes the XML document, or how it sends it; I can find the URL I should send requests to, but I can't figure out how the tool fires the XML off to that URL.
Notation problems aside, the other problem with the current method is that it was written in such a way that automating it as-is is seemingly impossible; it requires someone to open the program and use it once the recommendations should be attached. While technically not a major problem in itself, the reason this process is done two hours later is because the person that does it is busy with other things until that time; combined with the below issue, it's a bit of a productivity bump.
Other problems are that the tool is quite slow, and will only become slower; it requires 15-20 minutes at present to attach shipping recommendations, and as I've been able to discern, this is because it runs on pure RBAR. It steps through the entirety of our order database, even old orders, and attaches recommendations to all of them every time it runs. This is definitely behavior that should be improved upon, in my opinion. Also, at present, the business owner would like to not only determine which shipping method is the cheapest, but also the method that will get the shipment there within the customers' expectations; right now, the tool only calculates the cheapest method, so further manual examination and verification is necessary to prune it down to getting the shipments to the destination on time.
*Ahem* May have ranted a bit there. The current tool feels like the bane of my existence at this job, because it runs this process AND a ton of other important processes here, and it's ALL in the same, horribly vague notation. I've gotten the two dozen or so object "a" references confused countless times already. In any case, the current tool works, but it could do so much better, I believe, hence my desire to improve upon it :hehe:.
- 😀
May 26, 2012 at 7:50 pm
OK, give all of that then I would say that SSIS is probably your best bet.
That said, don't expect it to be a walk in the park, SSIS has a pretty steep ramp-up and it's not likely to go easily at first. But SSIS's advantage in this case is that it is the only tool here that has a built-in way to do your steps #3 & 4. (though I'd still expect a bit of head-banging).
Any other approach is going to require you to break open Visual Studio and start doing some serious things there to be able to tie #3 & 4 into everything else. If it were me, that's what I would do, but I already know Visual Studio, so I wouldn't be totally out of my skill zone.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2012 at 10:16 am
Success!
I managed to find someone that had written up a mock method of sending XML requests to UPS at the following link:
http://forums.asp.net/post/566897.aspx
Quite old code, and it needed some hammering out to fit my needs (it can't process files right off the top, but a little tinkering with StreamReaders in C# got it working the way I needed it to), and now I'm able to compose an XML file from SQL Server, export it, and have a C# automated routine om-nom-nom the XML and get a resultant XML string. All that's left is to figure out how to write those results into a separate file and leave them somewhere for SQL server to regularly pick them up and incorporate them.
Whew... This was a kinda awesome project overall :D. I still don't have too much of an idea about C#, but doing the research was interesting, and I managed to get it applied quickly. I can get a sense of how having an application designer would help a ton in a case like this, though... I don't think I'd manage to get anywhere productive if I kept hacking at C#, but I could do the SQL side of things just fine if someone managed that :hehe:.
Thanks a ton for all the help in this thread; I was definitely pointed in the right direction here, and it got me where I needed to be a lot faster than I would've managed myself. Now to go off and test like crazy to see what I can cook up with this! :w00t:
- 😀
May 30, 2012 at 10:08 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply