Bulk loading XML with SQLXMLBulkload

[James Beresford]

I was recently asked to help out with loading some awkward XML into SQL Server using the SQLXMLBulkLoad feature of SQL Server (it’s been there since 2000 but is one of SQL Server’s lesser known features). It’s a more graceful approach than using the XML Source Component in SSIS but has its own limitations and frustrations.

The specific problem I was trying to resolve was loading a file that had an Element that only had sub-Elements and contained no data, which resulted in me getting* the error “Schema: the parent/child table of the relationship does not match”

What is SQLXMLBulkload?

Apart from a mouthful with a vowel shortage, it is a standard component of SQL Server that provides a means to bulk load XML into standard relational tables. All you need is the SQL Server target tables, a script to invoke the SQLXMLBulkLoad, the XML file itself and an appropriately formed XSD schema to describe the XML and how to load it into the relational schema. The biggest limitation is that you cannot manipulate the data as it is imported – it is after all a bulk loader. The frustration part comes in forming the XSD schema properly, which can be a bit of a black art.

How to Invoke SQLXMLBulkload

The script to call the bulk loader is pretty simple – my example below was simply saved in notepad with a .vbs extension, which is then executed with a double click (or could be called from SSIS using an Execute Process task).

Set objBL = CreateObject(”SQLXMLBulkLoad.SQLXMLBulkLoad”)
objBL.ConnectionString = “Data Source=SERVER\INSTANCE_NAME;Initial Catalog=TARGET_DATABASE;Provider=SQLNCLI10.1;Integ rated Security=SSPI;”
objBL.ErrorLogFile = “C:\error.log”
objBL.Execute “C:\sample.xsd”, “C:\sample.xml”
Set objBL = Nothing

Handling Elements with sub-Elements only with sql:is-constant

Now, to the problem. My XML source contained a nested Element which had no content itself, but had further multiple occurences of a nested Element within it, as below:

nutty1.jpg
nutty2.jpg




nutty3.jpg
nutty4.jpg

For ages I was going in circles trying to get the relationship set between the Nut and Photo elements (using the sql:relationship annotation) but had endless trouble because each nesting demanded a relationship – so it looked like it needed a Nut > Photos > Photo relationship, but because the Element had no data items there was nothing on which I could establish a relationship. Eventually I stumbled upon the sql:is-constant annotation which tells the XSD schema that the element doesn’t map to a database table or column – and one of its documented uses is specifically to create a container element, as my situation required. So my final XSD looked like this:

One tiny bit of annotation solved my headaches – understanding the flow of XML and how XSDs interpret it is definitely tricky, but it can be very powerful. Of course, the fact that this took me four hours to solve using SQLXMLBulkload, but 5 minutes in SSIS took the colour off it a bit, but SSIS can’t always be used, as was the case for the person I helped out.

The SQLXMLBulkLoad documentation on MSDN is actually a good reference and well worth spending a little time going over so you can better understand this feature.



Get More from the original blog...