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!!

Advertisements

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

  1. […] SQL ← XML Transformation and SQLXMLBulkLoad using C#: Part 1 […]

  2. delhiescortservices4 says:

    escort services delhi

    escort services delhi

    Thanks for showing up such fabulous information. I have bookmarked you and will remain in line with your new posts. I like this post, keep writing and give informative post…!

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: