In order to parse XML data you need to have it ready in your SQL server. Since you will not always want to use SSIS or the Import Wizard let’s have a look at 2 different ways to import XML data into a table using TSQL.
Probably the most common and easiest way to load any sort of BLOB data into your SQL server is the OPENROWSET function that comes with SQL server. To utilize it you need to ensure that it is enabled. Use the following code to enable it:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO EXEC sp_configure 'show advanced options', 0 RECONFIGURE GO
Once that is taken care of you can import any XML file residing on your machine into a table. Let’s assume you have an XML file in your C: drive in a folder called SQL with the filename Collection.xml. The code below will insert it into a temporary table which we create on the fly:
/* create temp table to store the XML data */ IF (SELECT Object_Id('tempdb..#XML_Data')) IS NULL BEGIN CREATE TABLE #XML_Data (RowID INT IDENTITY(1,1), XML_Data_Column XML, ts datetime default getdate()); END ELSE BEGIN TRUNCATE TABLE #XML_Data; END; /* load a locally saved XML file to a temp table */ BEGIN TRY /* use OPENROWSET and open file as a BLOB */ INSERT INTO #XML_Data(XML_Data_Column) SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM OPENROWSET(BULK 'C:SQLCollection.xml', SINGLE_BLOB) AS x; SELECT * FROM #XML_Data END TRY BEGIN CATCH /* display error message */ SELECT ERROR_NUMBER() ErrorCode , ERROR_MESSAGE() ErrorMsg; END CATCH
You can now query the column XML_Data_Column and display the file you just loaded.
Not always does the XML data reside on your server or local machine, often times it needs to be retrieved from the internet using various APIs out there. Even though this is a little more complicated than using OPENROWSET there is no need to worry, SQL server got you covered. Using OLE Automated Stored Procedures you will be able to retrieve your data directly with a few lines of TSQL code. Once again we need to ensure that the functionality is enabled in our SQL server, to make sure it works use the code below:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE GO EXEC sp_configure 'show advanced options', 0 RECONFIGURE GO
In order to make the method call we need to do a few things:
- we need to create an object
- we need to open this object
- we need to let the object know what exactly we want to do
- we need to send the request
- optionally we can check the response status to see if our request was successful
- finally we need to save the response to a table.
Now this seems to be an awful lot to do, but it allows us very conveniently to do everything straight out of SQL server without having to set up a retrieval process outside of our code, so jumping through a few extra hoops might be worth it.
Let’s have a look at an example. The code below makes an API call to the website www.boardgamegeek.com and retrieves a list of all board games I own and have actually found the time to play. Since the site usually does not send the response we are looking for in the first go – they send an acknowledgement response first – we need to put the request in a loop until we get the output we really want. The code looks like this:
/* declare variables needed during the method call */ DECLARE @URL VARCHAR(8000), @Obj int, @HTTPStatus int, @i int; /* create temp table to temporarily store the xml response */ IF (SELECT Object_Id('tempdb..#XML_Responses')) IS NULL BEGIN CREATE TABLE #XML_Responses (RowID INT IDENTITY(1,1), XML_Data XML, HTTPStatus int, ts datetime default getdate()); END ELSE BEGIN TRUNCATE TABLE #XML_Responses; END; /* the call might have to be repeaded since the first time the website usually responds with a message stating it accepts the request, the second call renders the XML */ BEGIN TRY WHILE ISNULL(@HTTPStatus,0) <> 200 BEGIN /* URL used to get the boardgame list from boardgamegeek.com for Thomas Schutte */ SELECT @URL = 'http://www.boardgamegeek.com/xmlapi2/collection?username=thrond&own=1&played=1' EXEC sp_OACreate 'MSXML2.XMLHttp', @Obj OUT EXEC sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false EXEC sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' EXEC sp_OAMethod @Obj, send, NULL, '' EXEC sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT /* store the result to our temp table */ INSERT INTO #XML_Responses(XML_Data) EXEC sp_OAGetProperty @Obj, 'responseXML.xml'; /* retrieve identity */ SET @i = @@IDENTITY; /* update HTTPStatus to our table */ UPDATE #XML_Responses SET HTTPStatus = @HTTPStatus WHERE RowID = @i /* wait for retry */ IF @HTTPStatus <> 200 BEGIN WAITFOR DELAY '00:00:02' END END; /* output all responses */ SELECT * FROM #XML_Responses END TRY BEGIN CATCH SELECT ERROR_NUMBER() ErrorCode , ERROR_MESSAGE() ErrorMsg; END CATCH
You can filter out any responses that do not have a HTTPStatus of 200 so you won’t save any trash in your DB, but for this example I decided it makes sense to leave it in there.
And there you have it. 2 ways to import XML using TSQL into your database. I am sure there are other ways (using a linked server connection comes to mind *ugh*) but for now these 2 will have to do.
Feel free to leave any feedback in the comments below and look out for Part 3 where I’ll show you how to get XML into your SQL server using SSIS.
Getting started with XML in SQL Server
Part 3 (not available yet) will use SSIS to achieve the same results as in Part 1 & 2
Part 4 (not available yet) will show you how to use XQuery and create XML files out of SQL Server