Playing Chicken With Common Sense

With a number of clients, I have been provided a sample format for a transactions along with instructions to develop a process to load it into a database as quickly and painlessly as possible.  This usually comes with the requirements that the XML data file can have any number of records from 1 to n.  This example demonstrates the most efficient and scalable way that I have found to solve this. Not to mention that it can be maintained without rebuilding the solutions each time the data format changes.

SQLXML Diagram

XML into SQL Server 2005 Example

To load data from an XML into SQL Server, can be done in 3 easy steps.

1. Setup SQLXML.

2. Application to perform loading.

3. Develop Mapping File.

1. Setup Instructions (SQLXML)

[Install]

1. Download SQLXML 3.0
    Note: if using SQL Server 2005, then SQLXML4.0 comes with it.

[Convert to .NET Compatible]

2. To use this library, it must be convert to a type library that is .NET compliant. To do this.

     a. Using a Command Prompt browse to C:\Program Files\Common Files\System\Ole DB

     b. Verify that xblkld3.dll or xblkld4.dll exists.

     c. Assuming you have some version of Visual Studio .NET installed, run

             [Drive]:\[Path of Visual Studio]\Bin\tlbimp.exe [xblkld3.dll or xblkld4.dll] 

          This will create a new library called SQLXMLBULKLOADLib.dll.

3. In Visual Studio, select "Add Reference" in the Solution Explorer

4. Browse to Location mentioned in 2.a

[Add to Component Service (COM)]

5. Goto Start > Control Panel > Administrative Tools > Component Services

6. Under Components, Add new Application called "Sql Xml"

7. Open newly created application, and drag-n-drop, the xblkld3.dll or xblkld4.dll into application

ComponentServices9

[Reference in .NET Solution]

8. Add a reference to SQLXMLBULKLOADLib.dll to your project.

2. Loader Application

The loader application takes 3 things to execute properly - connectionstring, mapping file location, and data file location.  The example that I have provided allows for the user to provide 2 and assumes the connection string is fixed.  Since SqlXml requires a connection string, it is assumed that a database exists and that you have rights to the database based on NTLM credentials of SQL user credentials provided in the connection string.  For this example we have created an empty database named "SqlXmlDemo", and set SqlXml Component to do the following

objBL.CheckConstraints = True  // Check for Database Constraints and throw appropriate errors
objBL.SGDropTables = True // Drop Tables referenced in Schema prior to loading
objBL.SchemaGen = True // Create the Database Schema, if it does not exist

C#: Loader Application

using System;
using System.IO;
using System.Collections;
using SQLXMLBULKLOADLib;
using System.Data.OleDb;
using System.Diagnostics;

namespace SQLXmlExample
{
class Program
{
[STAThread]
static void Main(string[] args)
{
string schema = string.Empty;
string datafile = string.Empty;
string connectionString = @"provider=SQLOLEDB;data source=localhost;database=SqlXmlDemo;Integrated Security=SSPI;";

for (int i = 0; i < args.Length; i++)
{
switch (args[i].ToLower())
{
case "-schema":
schema = args[i + 1];
break;
case "-datafile":
datafile = args[i + 1];
break;
}
}
if (schema == string.Empty || datafile == string.Empty)
{
Console.WriteLine("Missing Schema or Data File. Format: SqlXmlExample -datafile [filename] -schema [filename]");
return;
}

Load(datafile, schema, connectionString);
}

static public void Load(string XMLFilename, string XMLMappingFilename, string ConnectionString)
{
SQLXMLBULKLOADLib.SQLXMLBulkLoadClass loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoadClass();
loader.CheckConstraints = true;
loader.XMLFragment = true;
loader.SchemaGen = true;
loader.SGDropTables = false;
loader.Transaction = false;
loader.ConnectionString = ConnectionString;
loader.Execute(XMLMappingFilename, XMLFilename);
}
}
}

VBScript: Loader Application

Dim objBL 
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=SqlXmlDemo;Integrated Security=SSPI;"
objBL.ErrorLogFile = "C:\ImportSample\error.log"

objBL.CheckConstraints = True
objBL.XMLFragment = True
objBL.SchemaGen = True
objBL.SGDropTables = True

objBL.Execute "C:\ImportSample\MappingFile.xml", "C:\ImportSample\DataFile.xml"
Set objBL = Nothing

3. Develop Mapping File.

The maintenance aspect of this process is 95% related to the mapping file.  The mapping file tells the application how the XML data elements map to the SQL Database Table.  The sample data file below is contains information about 3 people.  The mapping file show below the data file. outlines how this data is mapping to the database.  Since the application has been instructed to drop tables and recreate them, the mapping file is in controll of hte structure of the tables in which the data will be loaded.

XML Data File

<?xml version="1.0" encoding="UTF-8" ?>
<Data>
<Record>
<TABLE_NM>CLIENTS</TABLE_NM>
<ERR>0</ERR>
<FIRST_NAME>John</FIRST_NAME>
<LAST_NAME>Smith</LAST_NAME>
<CITY>Houston</CITY>
<STATE>TX</STATE>
<ZIP>77082</ZIP>
<DOB>1973-07-13</DOB>
</Record>
<Record>
<TABLE_NM>PROSPECTS</TABLE_NM>
<ERR>-1</ERR>
<FIRST_NAME>James</FIRST_NAME>
<LAST_NAME>Walton</LAST_NAME>
<CITY>Dallas</CITY>
<STATE>TX</STATE>
<ZIP>77563</ZIP>
<DOB>1965-01-15</DOB>
</Record>
<Record>
<TABLE_NM>CLIENTS</TABLE_NM>
<ERR>0</ERR>
<FIRST_NAME>Jane</FIRST_NAME>
<LAST_NAME>Doe</LAST_NAME>
<CITY>Austin</CITY>
<STATE>TX</STATE>
<ZIP>77342</ZIP>
<DOB>1961-12-31</DOB>
</Record>
</Data>

XML Mapping File

 
 
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="TABLE_NM" dt:type="string" />
<ElementType name="ERR" dt:type="int" />
<ElementType name="FIRST_NAME" dt:type="string" />
<ElementType name="LAST_NAME" dt:type="string" />
<ElementType name="ADDRESS" dt:type="string" />
<ElementType name="CITY" dt:type="string" />
<ElementType name="STATE" dt:type="string" sql:datatype="nvarchar(2)"/>
<ElementType name="ZIP" dt:type="string" />
<ElementType name="DOB" dt:type="date" />

<ElementType name="ROOT" sql:is-constant="1">
<element type="Record" />
</ElementType>

<ElementType name="Record" sql:relation="PERSON">
<element type="TABLE_NM" sql:field="TABLE_NM" />
<element type="ERR" sql:field="ERROR_ID" />
<element type="FIRST_NAME" sql:field="NAME_FIRST" />
<element type="LAST_NAME" sql:field="NAME_LAST" />
<element type="ADDRESS" sql:field="ADDRESS_LINE1" />
<element type="CITY" sql:field="CITY" />
<element type="STATE" sql:field="ST" />
<element type="ZIP" sql:field="ZIP" />
<element type="DOB" sql:field="BIRTH_DT" />
</ElementType>

</Schema>
 

Result

The result is a database with a newly created "PERSON" table and the data from the data file above.

Database Summary from SQL Server 2005

SQLServerDatabaseSummary2

PERSON Table after execution Loader Application

SQLServerTableData3

  • 2 Comments
  • E-mail
  • Kick it!
  • Shout it
  • Bookmark and Share

Comments

Bob Bob   United States 7/28/2009 11:11:47 AM #

I have been reviewing your project here and was wondering if you could explain the XML files you used for this project? I think your mapping xml file is a proprietary mapping format, is that correct? I was hoping to use just a XSD file. I tried that and it does not work. Is this possible somehow with your solution? Any assistance would greatly be appreciated!! Thanks!

Brad Merrell Brad Merrell   United States 7/28/2009 11:18:02 AM #

Bob,

   You are partially correct.  The schema elements are outlined in the "xmlns:sql="urn:schemas-microsoft-com:xml-sql" schema definition.  However, I designed the schema shown and the sample data for this demo based on a table that I put together and a simple XML that I created.  

In the schema, there are 2 sections:
1. The schema for the incoming XML
2. The schema of the database table which the data will be loaded into.

Control panel

RecentComments

Comment RSS