Category Archives: SQL

TSQL SHOWPLAN_ALL Commands

Hi again,

In my job I usually need to work with tsql and every query must have the best performance possible, that’s why I used to use the tool within the sql management studio named Graphical Execution Plans but I never know about the tsql commands needed to do it, but here are:

USE AdventureWorks;
GO
SET SHOWPLAN_ALL ON;
GO
-- First query.
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber LIKE '1%';
GO
SET SHOWPLAN_ALL OFF;
GO

This commands will show something like this:

Source: http://msdn.microsoft.com/en-en/library/ms187735.aspx

Advertisements

Accessing Data using DBISAM ODBC Driver with C#.NET

Hello again,

My work is always an adventure, and it’s fun too. I’m currently working with a project where I need to connect to a DBISAM Tables (dat files) to get some data so I thought is a good idea to create a post about it.

The first thing you need to do is to download the DBISAM Drivers (in case you don’t have it installed), I downloaded from here (you need to register first!)

I downloaded the file DBISAM-ODBC-TRIAL – DBISAM ODBC Trial

After the installation we need to configure our data source, for this we will use the ODBC Data Source Administrator we have in Windows, just go to Administrative Tools and open the ODBC (Data Sources) program.

NOTES (In case you have Windows 7 64-bit)
Because I have the Windows 7 Pro 64-bit version I need to use the 32-bit ODBC Data Source Administrator Tool because the drivers are in 32-bits for more information please check this article.

So we are ready to use this data source in our program. First we need to import the libraries we will use.

using System;
using System.Data.Odbc;

As you can see I imported the ODBC library from System.Data Assembly to use this class to connect to our data source. The next step is to create the OdbcCommand that have the SQL query we want to use.

string queryString = "SELECT * FROM machine";
OdbcCommand command = new OdbcCommand(queryString);

Then if we want to access to the data within the data source we need to create a connection, right?

string connectionStg = "PROVIDER=MSDASQL;DSN=MacDB;UID=admin;PWD=;";
OdbcConnection connection = new OdbcConnection(connectionStg)

Now we have a connection using a connection string we created (please note that the DSN is the name we created for the data source in the ODBC Data Source).

The final step is to execute our query and display the results:

using (OdbcConnection connection = new OdbcConnection(connectionStg))
{
    command.Connection = connection;
    connection.Open();
    OdbcDataReader reader = command.ExecuteReader();

    int fCount = reader.FieldCount;
    while (reader.Read())
    {
        for (int i = 0; i < fCount; i++)
        {
            Console.WriteLine(reader.GetValue(i).ToString());
        }
    }

    reader.Close();
    command.Dispose();
}

And that’s it, working and clean!

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!

XML Transformation and SQLXMLBulkLoad using C#: Part 1

Recently I was working on an import project that should import xml data from a third application to our database to make some reports.

The problem with this was that the xml documents from the third application had a format that wasn’t easy to read (At least didn’t have the format I wanted) so my first step was to make a XML Transformation that is basically a fancy name to transform an input xml to an output document (this output document can be a xml document or data stream).

In C# we have many classes to deal with XML, like XPathDocument, XslTransform (you could use XsltArgumentList too if you want apply some arguments to the xml transform) and XMLTextWriter.

This is an example of the XML Document from the third application, I named it Document.xml and saved it inside the \bin\Debug folder to be accesible from code)

<report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<reportlocaldata>
		<legendicons>
			<added>images/added.png</added>
			<notranked>images/notfound.png</notranked>
			<dropped>images/dropped.png</dropped>
			<movedup>images/moved-up.png</movedup>
			<moveddown>images/moved-down.png</moveddown>
			<notchanged>images/not-ranked.png</notchanged>
		</legendicons>
	</reportlocaldata>
	<reportheader>
		<title>Advanced Web Ranking Report</title>
		<project>LawtonWitchitafalls</project>
		<date>Sep 22, 2010</date>
	</reportheader>
	<group>
		<name>Bing</name>
		<seimagepath>images/bing.gif</seimagepath>
		<seurl>http://www.bing.com/search?first=0&amp;q=&amp;count=10&amp;mkt=en-us</seurl>
		<subgroup>
			<name>alejandrodotnet.wordpress.com</name>
			<color>#000000</color>
			<chartimagepath>2010-09-22_a2558413ebfe8b4f6404a012d9b1c3ee/Live.com-008dd47bfa4476ce8a7df1f009987049.png</chartimagepath>
			<item>
				<keyword>lawton blog</keyword>
				<competition>51900</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=lawton+blog&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#000000</urlcolor>
				<highlight>false</highlight>
				<url>https://alejandrodotnet.wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>exhaust</keyword>
				<competition>41000000</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=exhaust&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#000000</urlcolor>
				<highlight>false</highlight>
				<url>https://alejandrodotnet.wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>exhaust blog</keyword>
				<competition>67100</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=exhaust+blog&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#000000</urlcolor>
				<highlight>false</highlight>
				<url>https://alejandrodotnet.wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>brake repair</keyword>
				<competition>27800000</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=brake+repair&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#000000</urlcolor>
				<highlight>false</highlight>
				<url>https://alejandrodotnet.wordpress.com</url>
				<position>1</position>
				<previous>0</previous>
				<page>1</page>
				<best>1</best>
			</item>
		</subgroup>
		<subgroup>
			<name>wordpress.com</name>
			<color>#009900</color>
			<chartimagepath>2010-09-22_a2558413ebfe8b4f6404a012d9b1c3ee/Live.com-f670ecb16f2a22b0a7e6b5affebd6137.png</chartimagepath>
			<item>
				<keyword>mufflers blog</keyword>
				<competition>51900</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=mufflers+blog&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#009900</urlcolor>
				<highlight>false</highlight>
				<url>http://wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>exhaust</keyword>
				<competition>41000000</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=exhaust&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#009900</urlcolor>
				<highlight>false</highlight>
				<url>http://wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>exhaust blog</keyword>
				<competition>67100</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=exhaust+blog&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#009900</urlcolor>
				<highlight>false</highlight>
				<url>http://wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
			<item>
				<keyword>brake repair</keyword>
				<competition>27800000</competition>
				<sekeyurl>http://www.bing.com/search?first=0&amp;q=brake+repair&amp;count=10&amp;mkt=en-us</sekeyurl>
				<keycolor>#000000</keycolor>
				<urlcolor>#009900</urlcolor>
				<highlight>false</highlight>
				<url>http://wordpress.com</url>
				<position>0</position>
				<previous>0</previous>
				<page>0</page>
				<best>0</best>
			</item>
		</subgroup>
	</group>
</report>

A large document don’t you think? but what if I only use a few items?, then I need to use some code to transform it.

The first step is to create a XSLT Document that will define the new Document we want to create, if you want, the transformed document.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
	<xsl:for-each select="report">
	<report>
	<xsl:for-each select="group">
		<group>
			<name><xsl:value-of select="name"/></name>
			<xsl:for-each select="subgroup">
			<subgroup>
				<name><xsl:value-of select="name"/></name>
				<xsl:for-each select="item">
					<item>
						<keyword><xsl:value-of select="keyword"/></keyword>
						<url><xsl:value-of select="url"/></url>
						<position><xsl:value-of select="position"/></position>
						<date><xsl:value-of select="/report/reportheader/date"/></date>
					</item>
				</xsl:for-each>
			</subgroup>
			</xsl:for-each>
		</group>
	</xsl:for-each>
	</report>
	</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

As you see I used some XML Elements(like xsl:template or xsl:for-each) and XML Tags to define the new document. This is the document we will use to do the transform.

Please save this XSLT Document into the project folder (I saved this document inside \bin\Debug folder to be accesible from code)

The second step is using C#, now we will read the source xml document and the xsl document to transform it and create a new one.

using System;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml;
namespace XML
{
   class Program
   {
     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;

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

Now you can execute this code and will return a new XML Document named Transformed.xml that will look like this:

<report>
  <group>
    <name>Bing</name>
    <subgroup>
      <name>alejandrodotnet.wordpress.com</name>
      <item>
        <keyword>mufflers lawton</keyword>
        <url>https://alejandrodotnet.wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>exhaust</keyword>
        <url>https://alejandrodotnet.wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>exhaust lawton</keyword>
        <url>https://alejandrodotnet.wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>brake repair</keyword>
        <url>http://www.alejandrodotnet.wordpress.com</url>
        <position>1</position>
        <date>Sep 22, 2010</date>
      </item>
    </subgroup>
    <subgroup>
      <name>wordpress.com</name>
      <item>
        <keyword>mufflers lawton</keyword>
        <url>http://wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>exhaust</keyword>
        <url>http://wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>exhaust lawton</keyword>
        <url>http://wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
      <item>
        <keyword>brake repair</keyword>
        <url>http://wordpress.com</url>
        <position>0</position>
        <date>Sep 22, 2010</date>
      </item>
    </subgroup>
  </group>
</report>

Now you have transformed your source XML Document to a new one!! Nice, isn’t?

But what if you need to get the date in yyyy-MM-dd format? Then you need to use the XsltArgumentList class and create a custom XSLT Functions

This means you need to edit the XSLT Document and the C# Code,

The first change is inside the xsl:stylesheet tag, you need to add a xmlns attribute with the class name you will use to create the custom xslt function.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:XSLConvertHelper="common:XSLConvertHelper">

After you declare this xslt function, you can use it inside the xslt document, for our purpose we want to format the date, so we will use this custom fuction to format this date

<date><xsl:value-of select="XSLConvertHelper:XSLTDateTimeTypeConvert(/report/reportheader/date)"/></date>

Then in C# you need to add a new class and a method to define the function, this method will accept the date as a parameter (as we defined in the xslt document)

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");
    }
}

The last thing is to use this new class to transform the xml document,

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

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

And here is the result (an extract) of the transformation

<item>
	<keyword>exhaust</keyword>
	<url>http://wordpress.com</url>
	<position>0</position>
	<date>2010-09-22</date>
</item>
<item>
	<keyword>exhaust lawton</keyword>
	<url>http://wordpress.com</url>
	<position>0</position>
	<date>2010-09-22</date>
</item>

Now we have the XML Transformed the next step will be to use the SQLSMLBulkLoad to bulk load the data to the database, but i will write another post about this!!