Category Archives: C#

How to use XSLT to generate dynamic XML

Hi Guys,

Long time from my last post, what can I say, I have been busy ūüėõ Ok but now here is a quick sample about how to use XSLT to generate dynamic HTML and maybe create emails to send, to create XML files, or generate a entire or partial page to render, basically is the way you want to generate dynamic content.

In case you don’t know what is XSLT please read this page (thanks Wikipedia!), and maybe take a look of some other examples to understand how big can be in case you want to be more technical please check the W3C document

I will explain a very basic sample of how to use it with C# but please tell me in your comments any doubt or problem you have.

To do this example I created a new Web Site project in VS with the default settings,

The first step is to work with the XSLT template, to do this we need to define what will be the master template and what sections will be generated dynamically, an example of a simple XSLT template could be something like this:

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/DataSet">
    <xsl:for-each select="Table">
      <xsl:variable name="blogUrl">
        <xsl:value-of select="BlogUrl"/>
      </xsl:variable>
      <xsl:variable name="imagesRootUrl">
        <xsl:value-of select="ImagesRootUrl"/>
      </xsl:variable>

      <h1>
        <xsl:value-of select="MainTitle"/>
      </h1>
      <xsl:choose>
        <xsl:when test="IsMonday">
          Today is Monday!
        </xsl:when>
        <xsl:otherwise>
          Today is not Monday!
        </xsl:otherwise>
      </xsl:choose>
      <br/>
      <img src="{$imagesRootUrl}07fig01.gif" alt="This is an image" width="550" height="374" />
      <br/>
      <p>
        Please visit my <a href="{$blogUrl}">blog</a>
      </p>
      <span>©2012 Alejandro Carmona</span>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

As you can see this XSLT template has some static text and some dynamic text too, this dynamic text will be generated with the XSLT Transform from C#.

The second step is to create a data source from where we will get the data to Transform the XSLT Template to a XML, in this case we’ll use a DataSet in C# like this:

DataSet DataSource = new DataSet("DataSet");
DataTable DataTable = DataSource.Tables.Add("Table");

DataColumn pkCol = DataTable.Columns.Add("Id", typeof(int));
DataTable.Columns.Add("BlogUrl", typeof(string));
DataTable.Columns.Add("ImagesRootUrl", typeof(string));
DataTable.Columns.Add("MainTitle", typeof(string));
DataTable.Columns.Add("IsMonday", typeof(bool));              
DataTable.PrimaryKey = new DataColumn[] { pkCol };

DataRow dr = DataTable.NewRow();

dr["Id"] = 1;
dr["BlogUrl"] = @"https://alejandrodotnet.wordpress.com";
dr["ImagesRootUrl"] = @"http://www.webreference.com/authoring/languages/xml/aspnet/chap7/";
dr["MainTitle"] = "Hello World from XSLT";
dr["IsMonday"] = DateTime.Today.DayOfWeek == DayOfWeek.Monday;

DataTable.Rows.Add(dr);

The third step is to Transform the XSLT using the Template and our DataSource to generate the output

XmlDataDocument xmlLocations = new XmlDataDocument(DataSource);
XslCompiledTransform xslTran = new XslCompiledTransform();

string template = string.Empty;
TextWriter sw;

using (XmlTextReader xmlReader = new XmlTextReader(Server.MapPath("~/App_Data/SampleTemplate.xml")))
{
    xslTran.Load(xmlReader, null, null);

    StringBuilder sb = new StringBuilder();
    sw = new StringWriter(sb);

    xslTran.Transform(xmlLocations, null, sw);
}

Finally the last step is to show the result (I’m using a Literal Control to display the result)

litResponse.Text = sw.ToString();

The final result looks like this:

Now you have the basic knowledge to use XSLT,

Here is the VS project in case you want to try https://skydrive.live.com/redir.aspx?cid=842c5fff52cea6ab&resid=842C5FFF52CEA6AB!1221&parid=root

Advertisements

App.config settings in C#

Hi again!

I usually work with web pages but the other day I worked with a simple application to get some data from a file using the ODBC driver, but this desktop application must have some settings that I need to configure, here is where app.config file have their special moment.

Work with the app.config is like the web.config file talking about web projects, so its easy and very helpful to create a program with settings.

We need to add an app.config file to our project, just click in the Project, Add, New Item and select Application Configuration File.

Open the file and it will be like this (without the key setting, that is my default setting I added to the file) :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="setting" value="HelloWorld!"/>
  </appSettings>
</configuration>

Then in code, we need the assembly we need to manage the configuration file:

using System.Configuration;

Actually is very easy, to read a configuration just do this:

// Read values from configuration
string setting = ConfigurationManager.AppSettings["setting"];
Console.WriteLine(setting);

To write a new value, remove an old one or modify an existent one, just do this:

// Write values from configuration
// Modify
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.AppSettings.Settings["setting"].Value = "Hello_World!";
config.Save(ConfigurationSaveMode.Full);
// Add
config.AppSettings.Settings.Add("modifDate", DateTime.Now.ToLongTimeString() + " ");
config.AppSettings.Settings.Add("modifDate2", DateTime.Now.ToLongTimeString() + " ");
config.Save(ConfigurationSaveMode.Full);
// Remove
config.AppSettings.Settings.Remove("modifDate2");
config.Save(ConfigurationSaveMode.Full);

Ok now you can create settings for your application,

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!

Windows Communication Foundation: Hello World Application in IIS

Hello again,

In this post I will talk about the WCF platform and how to make an easy Hello World project and mounted in IIS 7.0

The first you need to know is that WCF is “…a platform rich in features necessary for building distributed service-oriented applications. With WCF developers can create services that satisfy a vast number of scenarios for the intranet and Internet including classic client-server applications, services that match the simplicity of ASP.NET web services (ASMX), services that incorporate the latest web service standards (WS*), and services that support advanced security scenarios such as federated security.”

I recommend to read this material to feel conformable with the terms around WCF but I can explain what are the basics Windows Communication Foundation Essentials

The basics of WCF are this:

  • A service Endpoint has an address, a Binding, and a Contract (ABC).
  • An Address is a network address indicates where the service is located.
  • A Binding specifies how a client can communicate with the endpoint including transport protocol (HTTP,¬†TCP,¬†Peer network,¬†IPC (Inter-Process Communication over named pipes),¬†MSMQ), encoding, and security requirements.
  • A Contract identifies what operations are available to the clients.

Ok it’s Code Time, let’s create a WCF Hello World Application, creating a New ASP.NET Web Application project with C#

First we need to define the Service Contract, named IHelloWorldService (yes it is an interface!)

using System.ServiceModel;

namespace HelloWorldWCF
{
    [ServiceContract]
    public interface IHelloWorldService
    {
        [OperationContract]
        string SayHello(string inputString);
    }
}

About the attributes: [ServiceContract] and [OperationContract], these are attributes that define an interface as a Service Contract and a method to be exposed as a operation of the service contract. In this example is a very simple WCF Service that have a SayHello operation.

Then we need to create a Service that will implement the Service Contract:

namespace HelloWorldWCF
{
    public class HelloWorldService : IHelloWorldService
    {
        public string SayHello(string inputString)
        {
            return "Hello " + inputString;
        }
    }
}

As you can see, the Service HelloWorldService is implementing the Service Contract IHelloWorldService with their Operations (SayHello)

We almost done!
The next step is to define a host, in our case we’ll mount the service in IIS (to see the different host options see this link)

To do this we need to create the .svc that is basically a web page that have only one line of code, something like this:

<%@ ServiceHost Language="C#" Debug="true" Service="HelloWorldWCF.HelloWorldService" %>

This simple line tells the svc file which Service will expose, in our case, we need to define the namespace and the Service class.

After that we need to add some lines to the web.config

<?xml version="1.0"?>
<configuration>
	<system.serviceModel>
		<services>
			<service name="HelloWorldWCF.HelloWorldService" behaviorConfiguration="ServiceBehavior">
				<endpoint address="" binding="basicHttpBinding" bindingConfiguration="" contract="HelloWorldWCF.IHelloWorldService"/>
				<endpoint contract="IMetadataExchange" binding="mexHttpBinding" bindingConfiguration="" address="mex"/>
			</service>
		</services>
		<behaviors>
			<serviceBehaviors>
				<behavior name="ServiceBehavior">
					<serviceMetadata httpGetEnabled="true"/>
					<serviceDebug includeExceptionDetailInFaults="False"/>
				</behavior>
			</serviceBehaviors>
		</behaviors>
	</system.serviceModel>
</configuration>

As you can see I added tag that will contain all the configuration of our service, then I define a Service with the tag (with a name and a behavior configuration, that it’s defined below inside the tag) and two end points, one for metadata exchange with the contract IMetadataExchange and a mexHttpBinding binding (perfect to share metadata of the service to build clases and share info about the Service you expose), and other one to expose the service, in our case using the basicHttpBinding binding and defining which contract we will use, the IHelloWorldService contract associated with the address in our web page (that’s why I leave it empty)

Finally you can test the service, just click on Start Debugging and will appear something like this:

To test this service we could use a tool that is integrated in Visual Studio 2008 and Visual Studio 2010, just try this:

  1. Open the tool WcfTestClient.exe (I found it in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE)
  2. Then Add a New Service on File > Add Service
  3. Paste the address of the service, in my case http://localhost:4286/HelloWorldService.svc
  4. Then the program will create the information with the service metadata
  5. You will see something like the image below (image 1)
  6. Click in the SayHello() method to test it and provide some parameters (image 2) and click on Invoke
  7. View the results (image 3)

The next post I will talk about an application using the Windows Phone 7 SDK!

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