Some time ago I started a blog post on how to use SQL XML Bulk Load for SQL Server, and this time around I’ll tackle some of the syntax for how to map a XML file to the Bulk Load.
When mapping an XML file to the schema for XML SQL Bulk Load the easiest method is to get an IDE (for example Visual Studio) to create the schema file for you based on your XML, if you don’t have the schema outright.
In this blog post, I’ll use
this XML file as an example, because it holds both how to read fields, attributes and relationships, and thus should cover many of the basics of the bulk load. I made
my schema file via Visual Studio, and once you have a schema file you can start mapping the elements to the database tables and columns.
The first things first is to import the namespace:
urn:schemas-microsoft-com:xml-sql into the file with a prefix. Usually I suggest (as others do) using "
sql" for this prefix, simply to illustrate clearly that the namespace is tied to the bulk import and not to confuse it with other namespaces.
Now –a word of warning. The bulk load can only insert values into the database, so the way I normally handle imports in a production environment is to make an "import structure" which mirrors the imported data and will act as temporary storage. Then I make some SQL scripts to moving the data over into the production tables from my temporary import structure.
This also allows for interjecting a sort of validation layer for your data if you choose.
For this xml import, I make this import structure in the database:
CREATE TABLE [dbo].[BulkLoadedProduct](
[InternalProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [nvarchar](200) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[BulkLoadedProductDetails](
[InternalProductID] [int] NOT NULL,
[LanguageCode] [varchar](5) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[ProductDescription1] [nvarchar](MAX) NOT NULL
) ON [PRIMARY]
Now the sharp observer will notice some inconsistency in the naming of my columns; it is quite intentional, because I’ll use it later to illustrate how to map elements to fields in two different methods.
Just to recap, the method of calling the Bulk Load was written in an earlier blog piece which can be found here.
So, down to business. The first keyword which is needed to be mentioned is the "is-constant".
This is used to indicate an element which is only appearing once per “relationship”, and which isn’t to be mapped to a field in the database.
So in my instance the “Products” root element is one of those, so I annotate this field with the is-constant="1" like this:
<xs:element Name="Products" sql:is-constant="1">
The next annotation which is important to know is "relation". This is used to map elements to a table in the database. So in this case, I want to map Products elements to the table BulkLoadedProduct which happens like this:
<xs:element Name="Product" maxOccurs="unbounded" sql:relation="BulkLoadedProduct">
After this we need to tell the bulk load where to put the field "ID" into the database. That’s done with the sql:field annotation like this:
<xs:element Name="ID" Type="xs:string" sql:field="ProductID" />
Right now I want to introduce an annotation called "mapped" which tells the bulk load that the field is not mapped to the database. So adding the sql:mapped="0" (or false if you’d rather use that) to an element then you’ll not get the field into the database.
So if we for example add the sql:mapped="0" to the element LanguageValues like this
<xs:element Name="LanguageValues" sql:mapped="0">
then it will tell the bulk load to ignore all child structure under that element.
But seeing as I want the details imported, I’ll skip that part myself - but it is useful for debugging purpose as well, so it is important to know.
So, the next problem is getting the details loaded. This is done with relationships.
So remove the mapped annotation and write the following in the top of your schema file. I personally prefer to keep the relationships in the top of my schema file for easier reference, but they can be added directly on the element. I’ll however only show the one way of doing it.
<xs:annotation>
<xs:appinfo>
<sql:relationship Name="Detail"
parent="BulkLoadedProduct"
parent-key="ProductID"
child="BulkLoadProductDetails"
child-key="ProductID" />
</xs:appinfo>
</xs:annotation>
This tells the bulk load to create a relationship between the two tables based on the parent/child key naming.
This relationship is then added to the LanguageValue element:
<xs:element maxOccurs="unbounded" Name="LanguageValue"
sql:relation="BulkLoadProductDetails" sql:relationship="Detail">
One thing to be wary of is that the more relationships you have, i.e. a more complex XML structure, the slower your import will be. So if speed is important, make your XML import file simple as possible, then you can load big files very fast.
Finally, as I mentioned earlier, you can map elements to columns in two different ways. One was via the “field” annotation, however if the element is called the very same as the column in the target table relation, you do not need to add a field. So, for the fields Name and LangaugeCode, I do not need to add any “sql:field” annotation because the Bulk Load automatically will map it to the fields in the table when they’re named the same. This can simplify a schema file tremendously, so it is worth remembering.
Anyways, putting it all together and I end up with the following schema file.
Running the XML through the XML SQL Bulk Load with the scehma file, and I end up with a result which looks like this:
(click for larger view)
and now I’ve successfully imported the data from my XML into the database.