Import XML into SQL Server

One thing any responsible fisherman needs is a pair of pliers.  You may need different sizes for different types of fish.  For panfish, something small and skinny is going to help get in their small mouths to extract deep hooks.  For muskie, you’re going to want an extra-long pair to get to the hooks while keeping your hands away from those viscous teeth.  I’ve found that Northern Pike are particularily good at swallowing hooks into the back of their throats.  The last thing to remember is that a cut in the gills is almost always a death sentence for the fish.  Since the gills are what re-oxygenates the blood, their is a high volume of blood flow there, and even a small cut can cause a fish to bleed out in minutes.  If you aren’t confident you can remove a deep hook without damaging the fish, it’s usually best to just cut the line as close to the hook as possible.  Studies have shown the fish are adept at shaking hooks off once they are free of the fisherman.


I recently was provided a file that I needed to extract a bunch of data from.  The problem was that the file was xml, not the easiest to import into SQL Server.  I took a two step approach to solve this problem.  The first step was to import the data into SQL Server as a blob:

CREATE TABLE dbo.MarketplaceDecemberXML
, LoadedDate DATETIME

INSERT  INTO dbo.MarketplaceDecemberXML
( XMLData
, LoadedDate
SELECT  CONVERT(XML , BulkColumn) AS BulkColumn
FROM    OPENROWSET(BULK '\\ServerName\DataImport\All Orders.txt' , SINGLE_BLOB) AS oro;

The next step is to pull out any data that is needed.  There are a few ways to do this, but the easiest for me was to use the OPENXML function.  This function required me to load the xml data into an xml variable, then I could read different data based on the depth to which I did the initial opening.

, @hDoc AS INT

SELECT  @XML = adx.XMLData
FROM    dbo.MarketplaceDecemberXML adx;
EXEC sys.sp_xml_preparedocument @hDoc OUTPUT , @XML;

SELECT  MarketplaceOrderID
, FulfillmentChannel
, PurchaseDate
, CustState
, OrderStatus
FROM    OPENXML(@hDoc, 'VendorEnvelope/Message/Order/FulfillmentData/Address')
VendorOrderID VARCHAR(100) '../../VendorOrderID',
OrderStatus VARCHAR(100) '../../OrderStatus',
FulfillmentChannel VARCHAR(100) '../FulfillmentChannel',
PurchaseDate DATETIME '../../PurchaseDate',
CustState VARCHAR(100) 'State'

EXEC sys.sp_xml_removedocument @hDoc;

Here is a screenshot of some of the data I was working with:


The important thing to note is that I used the ../ to move up in the hierarchy.  Using this method I was able to extract all the data I needed and place it in a table where I could query it at will.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s