I suspect, that it's something to do with my feed server's config however, so need to do some work to resolve this, I thought I'd add the code here for thank's for the suggestion, but that's how I currently do the task, I have a CLR binary that I wrote, that runs once a day to sync the feed to the DB, but it takes too long to sync the data. The code above however does work with correctly formatted XML from the internet, for example 'geonames' (which is what the original source of the above code was based on) works fine.
#Dtsql download full
If I use the full URL, which returns pure XML data with a mime type of 'text/xml' then response text contains nothing, it's null but if I strip it back to say ' or ' so that the web server is just passing a 404 page or default html page, then I get the actual page content in.Īt first I thought it might be the mime type, 'text/xml' vs 'text/html' but testing that made no difference, anything that returns valid XML seems to give null, but anything that returns broken XML seems to work!! Select = ' exec sp_OACreate 'MSXML2.XMLHTTP', OUT Įxec sp_OAMethod 'open', NULL, 'get', 'false'Įxec sp_OAMethod 'responsetext', OUTPUTĮxec sp_OADestroy sp_xml_preparedocument OUTPUT, seems to work, and work quite efficiently, however here's the weird thing. What I'm wanting to know, is instead of using a 3rd party program to get this data from the service then call the stored proc and insert it, is it in anyway possible for me to get SQL Server to just grab the XML directly from the service URL and process it like that.Īll I can seem to find no matter how much I try is plenty of articles & posts on reading / writing files to / from the server file system and the several million or so on actually inserting the XML data, but I can't seem to find anything on getting the server to grab data directly from a URL.Īfter a little bit more research Iv'e found a third way to do this: declare as int
Getting the XML into a table is no problem I can already do that, but what I'm having to do at the moment is pass a huge long string of XML to a stored proc which then uses the OPENXML command to insert it into the table. The data in these services needs to be mirrored into a Table on an SQL Server 2008 instance.
The book also emphasizes the need to have a correct understanding of the language and its underlying mathematical foundations.I have a set of XML based services running on my network. There’s a deep focus on the performance and efficiency of the techniques and solutions covered. This book covers many unique techniques that were developed, improved, and polished by the authors over their many years of experience, providing highly efficient solutions for common challenges. Deepen your understanding of architecture and internals-and learn practical approaches and advanced techniques to optimize your code’s performance. Tackle the toughest set-based querying and query-tuning problems-guided by an author team with in-depth, inside knowledge of T-SQL. It includes coverage of SQL Server 20, but in many cases deals with areas that are not version-specific and will likely be relevant in future versions of SQL Server. Updating both Inside Microsoft SQL Server 2008: T-SQL Querying (Microsoft Press, 2009) and parts of Inside Microsoft SQL Server 2008: T-SQL Programming (Microsoft Press, 2009), this book gives database developers and administrators a detailed look at the internal architecture of T-SQL and a comprehensive programming reference.
0 kommentar(er)
