XML Transformation and SQLXMLBulkLoad using C#: Part 2

In the last post I talked about the XML Transformation I used for an import project, now I continue with the SQLXMLBulkLoad using the XML Transformed Document as a source.

The first thing to do is to create the tables used to import the information, I will create three tables to save the information from the XML Document.

-- CREATE DATABASE FOR THIS EXAMPLE
CREATE DATABASE SQLXMLBULKLOAD
GO
USE SQLXMLBULKLOAD

-- TABLE TO SAVE GROUPS
CREATE TABLE [dbo].[SQLXML_BULKLOAD_Group](
	[GroupID] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_SQLXML_BULKLOAD_Group] PRIMARY KEY CLUSTERED
(
	[GroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- TABLE TO SAVE SUBGROUPS
CREATE TABLE [dbo].[SQLXML_BULKLOAD_SubGroup](
	[SubGroupID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NOT NULL,
	[SubGroupName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_SQLXML_BULKLOAD_SubGroup] PRIMARY KEY CLUSTERED
(
	[SubGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- FK IN SUBGROUPS TO TABLE GROUPS
ALTER TABLE [dbo].[SQLXML_BULKLOAD_SubGroup]  WITH CHECK ADD  CONSTRAINT [FK_SQLXML_BULKLOAD_SubGroup_Group] FOREIGN KEY([GroupID])
REFERENCES [dbo].[SQLXML_BULKLOAD_Group] ([GroupID])
ON DELETE CASCADE

ALTER TABLE [dbo].[SQLXML_BULKLOAD_SubGroup] CHECK CONSTRAINT [FK_SQLXML_BULKLOAD_SubGroup_Group]

-- TABLE TO SAVE ITEMS
CREATE TABLE [dbo].[SQLXML_BULKLOAD_Item](
	[ItemID] [int] IDENTITY(1,1) NOT NULL,
	[SubGroupID] [int] NOT NULL,
	[ItemKeyword] [varchar](100) NOT NULL,
	[ItemUrl] [varchar](1024) NOT NULL,
	[ItemPosition] [tinyint] NOT NULL,
	[ItemDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SQLXML_BULKLOAD_Item] PRIMARY KEY CLUSTERED
(
	[ItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- FK IN ITEMS TO TABLE SUBGROUPS
ALTER TABLE [dbo].[SQLXML_BULKLOAD_Item]  WITH CHECK ADD  CONSTRAINT [FK_SQLXML_BULKLOAD_Item_SubGroup] FOREIGN KEY([SubGroupID])
REFERENCES [dbo].[SQLXML_BULKLOAD_SubGroup] ([SubGroupID])
ON DELETE CASCADE

ALTER TABLE [dbo].[SQLXML_BULKLOAD_Item] CHECK CONSTRAINT [FK_SQLXML_BULKLOAD_Item_SubGroup]

Then the second thing you need to know is that we will use a COM library, so we need to include this library to our project. In case you don’t have this library installed in your computer download here,

  1. From Project menu, select Add Reference.
  2. In the COM tab, select Microsoft SQLXML Bulkload 3.0 Type Library (xblkld3.dll) and click OK. You will see the Interop.SQLXMLBULKLOADLib assembly created in the project.

Then we can use the class SQLXMLBulkLoad3Class to perform this xml bulk load, but we need a xsd schema file to define the SQL XML import that will map the xml fields to the sql fields in the database.

This is the xsd schema file I created to work

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
		   xmlns:xs="http://www.w3.org/2001/XMLSchema"
		   xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
		   >
	<xs:annotation>
		<xs:appinfo>
			<sql:relationship name="SubGroupGroupID"
							  parent="SQLXML_BULKLOAD_Group"
							  parent-key="GroupID"
							  child="SQLXML_BULKLOAD_SubGroup"
							  child-key="GroupID" />

			<sql:relationship name="ItemSubGroupID"
							  parent="SQLXML_BULKLOAD_SubGroup"
							  parent-key="SubGroupID"
							  child="SQLXML_BULKLOAD_Item"
							  child-key="SubGroupID" />
		</xs:appinfo>
	</xs:annotation>

  <xs:element name="report" sql:is-constant="1">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="group" sql:relation="SQLXML_BULKLOAD_Group">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="name" type="xs:string" sql:field="GroupName" />
              <xs:element maxOccurs="unbounded" name="subgroup" sql:relation="SQLXML_BULKLOAD_SubGroup" sql:relationship="SubGroupGroupID">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="name" type="xs:string" sql:field="SubGroupName" />
                    <xs:element maxOccurs="unbounded" name="item" sql:relation="SQLXML_BULKLOAD_Item" sql:relationship="ItemSubGroupID">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="keyword" type="xs:string" sql:field="ItemKeyword" />
                          <xs:element name="url" type="xs:string" sql:field="ItemUrl" />
                          <xs:element name="position" type="xs:byte" sql:field="ItemPosition" />
			  <xs:element name="date" type="xs:date" sql:field="ItemDate" />
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

As you can see I created this XSD Schema Document using XSD Elements and sql relationships using Annotations (to map relations between tables) and xs elements to define fields inside sql relations (tables).

For more information about this please check link http://msdn.microsoft.com/en-us/library/aa192198(office.11).aspx

Ok, so let me show you the C# code you need to make this work,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml;
using SQLXMLBULKLOADLib;

namespace XML
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            // Creating the XPath Doc
            XPathDocument myXPathDoc = new XPathDocument("Document.xml");
            // Load the XSL used to Transform the XML Doc
            XslCompiledTransform myCompiledTrans = new XslCompiledTransform();
            myCompiledTrans.Load("data.xsl");
            // This document will have the Transformed XML Doc
            XmlTextWriter myWriter = new XmlTextWriter("Transformed.xml", null);
            // Set Formatting.Idented to have a nice Idented XML Document
            myWriter.Formatting = Formatting.Indented;

            XsltArgumentList xslArguments = new XsltArgumentList();
            xslArguments.AddExtensionObject("common:XSLConvertHelper", new XSLConvertHelper());

            // Do the XML Transform and save the new XML Document
            myCompiledTrans.Transform(myXPathDoc, null, myWriter);
            myWriter.Close();

            // This is the new code
            // Using the class to use SQL XML Bulk Load (version 3.0)
            SQLXMLBulkLoad3Class objBL = new SQLXMLBulkLoad3Class();
            objBL.ConnectionString = "Provider=sqloledb;server=ACARMONA\\SQLEXPRESS;database=SQLXMLBULKLOAD;integrated security=SSPI";
            objBL.ErrorLogFile = "errorLog.xml";
            objBL.KeepIdentity = false;
            objBL.XMLFragment = true;
            objBL.Execute("data.xsd", "Transformed.xml");

            Console.ReadKey();
        }
    }

    public class XSLConvertHelper
    {
        public string XSLTDateTimeTypeConvert(string data)
        {
            // parameter data example:
            // Jul 22, 2010
            DateTime date = DateTime.Parse(data);
            return date.ToString("yyyy-MM-dd");
        }
    }
}

As you see is a very simple code, the tricky thing here is to add the attribute [STAThread] to the main method, because I had some errors without it. Basically I found that this attribute is only needed when you are working with COM Interop (see this link).

 

The SQLXMLBulkLoad3Class object has many options but I used the most important like ConnectionString (Identifies the OLEDB connection string that provides the necessary information to establish a connection to an instance of the database.), ErrorLogFile (Specifies the file name into which the XML Bulk Load logs errors and messages.), KeepIdentity (Specifies how to deal with the values for an Identity type column in the source file.), XMLFragment (Specifies whether the source data is an XML fragment) and the Execute (Bulk loads the data by using the schema file and data file (or stream) that are provided as parameters.) to make this work (If you need more info please check this link).

Then I run the program you can see the results:

As you can see the data has been imported successfully!

So we start this with a XML file from a third application, we transform this XML and then we use this new file to import the data to our datatables, cool isn’t? Now we are ready to make some reports!

Advertisements

8 thoughts on “XML Transformation and SQLXMLBulkLoad using C#: Part 2

  1. Azul Islas says:

    This was quite useful!!!!! thanks!!! =) finally the solution i was looking for

  2. rene says:

    interesting, yet, how does it perform compared to bulk loading on SQL and openxml or using SSIS?

  3. hi i dont have the library “Microsoft SQLXML Bulkload 3.0 Type Library (xblkld3.dll)” i follow your download link but not find any link for this dll, should i have to install the SQL Analysis Services which is not currently installed

    • Alejandro Carmona says:

      Hi Ahmed,

      Thanks for your comment, I think that get the last version of the SQLXML LIbrary could work, let me know if I can help you.

  4. yup i need your help! i dont have enough idea to work with XML, i am getting this error
    “Schema: unable to load schema ‘XMLEmployeeSchema.xsd’. An error occurred (Unknown Error).”

    SQL Table some thing like this: (Employee)

    CREATE TABLE [dbo].[Employee](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EmployeeCode] [varchar](max) NOT NULL,
    [EmployeeName] [varchar](max) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [DepartmentName] [varchar](max) NOT NULL,
    [EmployeeGroup] [varchar](max) NOT NULL,
    [Email] [varchar](max) NOT NULL,
    [isActive] [bit] NOT NULL

    And my XSD Schema: (XMLEmployeeSchema.xsd)

    And the XML: (Employee.xml)

    1
    113561
    Shemeer
    1
    IT
    Admin
    shemeer@xyz.com
    1

    And C# Code which i am using

    protected void XMLtoSQL2008_Click(object sender, EventArgs e)
    {

    SQLXMLBulkLoad4Class objXBL = new SQLXMLBulkLoad4Class();
    objXBL.ConnectionString = “Provider=sqloledb;server=MININT-UF5D4EL;database=CompanyInfo;uid=sa;pwd=aptech”;
    objXBL.KeepIdentity = false;
    objXBL.XMLFragment = true;
    objXBL.Execute(“/XML/XMLEmployeeSchema.xsd”, “/XML/Employee.xml”);

    }
    i have tiered to resolve it and ask it to resolve from my friends as well but no success yet

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: