Incoherent Rambling

Globalization / Localization Strikes Again

2008-09-28T06:56:31Z

To assume, as they said it, is to make an ass of u (you) and me.

And that's exactly what happened recently on one of my side projects. I created a stock quote component that will retrieve stock data in form of a CSV from Yahoo! Finance.

Take a look at the following ASP.NET code fragment example:

   1:  string numberExtractedFromAnExternalCSV = "1834.591";

2:

   3:  Decimal number = Convert.ToDecimal(numberExtractedFromAnExternalCSV);

4:

   5:  Response.Write(number); //expecting 1834.591

   6:  Response.Write(string.Format("{0:N0}", number));  //expecting 1,835

On a couple computers that I ran this code on, I got what I was expecting. So I assumed that since it did well on the computers that were under my control (and it actually ran correctly on one of the web hosting that we tried), it should run well everywhere. But when the thing is deployed to a different web hosting, I got something totally different.

The result appeared as 1834591 and when formatted, it became 1.834.591.

In a smaller font, I didn't quite realize what was happening since the different between . (period) and , (comma) is not too discernable. All I know was that my number was appearing funny and off compared to the real quote from the actual source.

I hypothesized that this might be something to do with the string.Format function or rather, I was using StringBuilder.AppendFormat. Perhaps there was some bug or something. From the look of it, the web hosting might be running Linux and executing the web application under Mono. Perhaps its a Mono bug.

Playing around with some test codes didn't produce any good conclusion. The test code was as follow:

   1:  Decimal number = 1834.591M;

2:

   3:  Response.Write(number); //expecting 1834.591

   4:  Response.Write(string.Format("{0:N0}", number)); //expecting 1835

Which you can imagine produced the expected result (or rather something a bit off, but not too discernable... 1834,591 and 1.835). This was confirmed when the test was deployed to the web hosting.

After staring at the screen for a while and realizing that the comma and period are in the wrong places, I realized that I am staring at a globalization / localization issue. Apparently the current web hosting is using Indonesian culture (id-ID) in their server Regional and Language Settings, while the web application itself was not associated with any default globalization setting which of course will pick up whatever the machine's localization was set to.

Forcing the web application into en-us in the web.config, fixed the issue.

<globalization culture="en-US" uiCulture="en-us" />

One thing that I like to point out is that Decimal.Convert was the culprit and I should have tested the result of that conversion first (1834.591 was converted to 1834591 since the period was ignored in id-ID culture setting).

Well, that's the third time globalization / localization has bitten me in the ...

Share this post: | | | |

Taking Linux For a Spin

2008-09-26T11:05:47Z

Was tasked to install Informix on a Linux recently. I've never really play around Linux that much. Occassionally, downloaded a distribution like Ubuntu or Mandrake (now Mandriva) and installed them on a VM and just looked around what the OS is about. Also played around with the bourne shell some, so I know some basic Linux commands like ls, cp, rm, cat, ps, grep, more, etc. and how to pipe them up together. Learned some of the Unix related commands on my previous job when I had to play around a little with Sun Solaris, which was like a good 11 years ago.

To my surprise, I found myself intrigued with the OS. Especially with the latest Ubuntu 8.04 (Hardy Heron). It's a pretty nice OS to use and quite easy compared to something like Red Hat / Fedora distribution.

In trying to make Informix installed correctly on some of the Linux distributions (tried Ubuntu and Fedora), I found myself learning quite a lot about this OS. For example, in Vista / Windows OS,we have the User Account Control and Run As Administrator. It turned out that Linux also has similar thing, such as sudo and su - root. Learned about where web server is installing its root web (var/www), and some other neat stuffs.

So, feeling intrigued, I decided to try it further by installing MySql, and Mono (naturally, being a .NET developer, I just have to see what's the big deal about Mono). Installed MonoDevelop and started hacking some code to connect it to MySql and query the data. There were some bumps in the road, but overall, I think it's a good thing that .NET can run on Mono. So far, tried doing generic, database connection stuffs. Haven't played around Silverlight implementation (Moonlight) yet.

In any case, it was an interesting experience. Would I throw away Windows for it completely? Probably not. I just can't live without Visual Studio, and Windows Live Writer, yet :). Maybe one day, hehehe. It does make a good alternative if you have a spare computer. I really want to try Beryl? That stuff looks pretty awesome for an eye candy, but just don't have any machine I can spare for a non VM installation yet.

Share this post: | | | |

Look Ma... No Wire!!

2008-09-26T10:47:45Z

Finally decided to try wireless networking at home. Bought a Linksys WRT54G2 wireless router for about IDR 570,000 and hooked it up to my home cable modem. After troubleshooting some issue like the router just couldn't get an IP address from the cable modem and Vista to Vista file sharing problem that stumped me for quite a while, I am a happy wireless networker now :).

Things that I found I enjoy doing on the new home network:

1. Browse the web from anywhere around the house

2. Access and transfer files from my laptop and home PC without needing to copy it to a flashdisk

3. Using my laptop and remote desktop as a remote control to play audio files on my home PC :)

4. Don't have to wait until my kid is finished playing Baby Smash, Sesame Street games, or whatever until I can use the net :)

Happy Happy Joy Joy!

Share this post: | | | |

K2 BlackPearl 807 is Out

2008-09-25T06:37:30Z

Finally, the long awaited 807 is out of the oven. Downloading it as I type. Quite big this one, 200+ mb. Out of Office is finally making a come back after some hiatus from K2.NET 2003 to BP transition period. Supposedly this version should fix the K2 report problem that has been plaguing the server at my office since upgrading to SP1 version. We'll have to see later on today.

Share this post: | | | |

Operation could destabilize the runtime

2008-09-22T09:00:00Z

I was trying to reproduce Repository pattern that Rob Conery was using in his MVC Store Front project in my own project.

Continuing with the interface programming style hype, I decided to make everything implement some sort of an interface. So my domain object (say Person), will implement IPerson interface and so on.

So I have an IPersonRepository that will have a couple of implementations. One for testing (TestPersonRepository : IPersonRepository) and a real Linq To Sql implmentation (SqlPersonRepository : IPersonRepository).

The repository has one method, which is IQueryable<IPerson> GetPersons() that will return all the available persons.

My test which was using the TestPersonRepository implementation works well. But when I tried integrating my code using the real Linq to Sql implementation, it blew up.

The code for the SqlPersonRepository implementation is as follow:

   1:  public IQueryable<IPerson> GetPersons()

   2:  {

   3:      var db = new Data.Linq.SampleDbContext();

4:

   5:      var query = from p in db.Persons

   6:                      select (IPerson) new Domain.Person {

   7:                        FirstName = p.FirstName, LastName = p.LastName

   8:                      };

9:

  10:      return query;

  11:  }

As you can see, I was trying to cast Person to IPerson in the Linq query. This apparently didn't work. Hehehe. Changing all my interface to IQueryable<Person> and removing the casting will make it work.

Another work around that I found to work is using the following code:

   1:  public IQueryable<IPerson> GetPerson()

   2:  {

   3:      var db = new Data.Linq.SampleDbContext();

4:

   5:      var query = from p in db.Persons

   6:                          select p;

7:

   8:      IList<IPerson> results = new List<IPerson>();

9:

  10:      foreach(var p in query)

  11:      {

  12:          results.Add(new Domain.Person { FirstName = p.FirstName, LastName = p.LastName });

  13:      }

14:

  15:      return results.AsQueryable();

  16:  }

Sucks :)

Anyone want to take a stab at what's going on here?

Share this post: | | | |

Interesting MVC Sample Project

2008-09-22T08:57:00Z

I was listening to some podcasts or other, I think it was one of the Herding Code podcasts, where they interview Rob Conery and his sample MVC project. In which he walked you through the process of creating this MVC Storefront using ASP.NET MVC, TDD, LINQ, Dependency Injections, etc. The format of it is a webcast that you can download and playback at your own leisure.

Along the way he consulted other experts in the .NET world such as Ayende Rahien (Oren Eini) of NHibernate and RhinoMock fame, Jon Galloway, etc.

If you are interested in doing MVC and this type of stuffs, I strongly suggest you take a look at his webcasts.

You can find the stuff here.

Rob is also the creator of Subsonic (data access layer scaffolder / mapper or something for lack of better term).

Share this post: | | | |

Reflecting over Software Usability

2008-09-22T08:53:00Z

I was entertaining my two and a half year old boy a couple days back. I'm just amazed on how quick kids nowadays can adapt to technology. At that age, he already mastered how to use the mouse to navigate, click and drag and drop stuffs.

Lately he's been hooked on Scott Hanselman's Baby Smash, YouTube kids videos like the Alphabet Song, etc. and Sesame Street website which is full of games and activities for kids his age.

I can give him instruction already on how to use most of the stuffs that are on the screen (i.e. go click that red button there, or just drag the lemon to the jug, etc.) and he could execute the instruction just fine. Recently, I don't even have to tell him how to switch between YouTube videos, etc. He'll do it by himself. As a matter of fact, he'd push me aside so not to disturb him having fun.

One thing that amazed me recently is that he seems to understand on how to use the environment like Vista without me telling him the stuffs up front. For example, one time we had FireFox running YouTube, Internet Explorer running a JavaScript HTML application that I built for him (bouncing box with numbers that keep changing color and a bunch of stuffs that you can do to the boxes) and a media player running one of his favorite videos. By accident, he discovered that if he hovered near the taskbar, Vista will give you a preview of the application in a thumbnail. As I watched him playing around with the mouse, I simply post a question to him asking if he could find out how to bring up the Sesame Street session (it was minimized at that time) and he instinctly knew that if he did the hover action, he could figure out which application is running Sesame Street and by clicking on it he was able to bring up IE to the front.

Now, I don't know if this is a testament of the UX brilliance of the Vista team or my kid is just plain smart, lol.

Just thought I share that with you all.

Share this post: | | | |

Apparently VS 2008 SP 1...

2008-09-22T08:28:00Z

Apparently, if you install Visual Studio 2008 Service Pack 1, you don't need to install .Net Framework 3.5 SP 1 anymore (already included). It also installed .Net Framework 2.0 SP 2 and .Net Framework 30. SP 2... Apparently.

Btw. What's in the SP2s? Can't find those online to download separately.

Share this post: | | | |

New Cool Stuffs in Team Foundation Server 2008

2008-09-22T08:23:00Z

Installed Team Foundation Server 2008 Workgroup Edition a couple of days back and been playing around with it.

One thing that I like about TFS 2008 is that they revamped the build process so now you can do Continuous Integration (build when you check-in) / Nightly, and other build settings right from inside VSTS / Team Explorer. Awesome!! :)

Share this post: | | | |

Tabular XML Data to Hierachical XML Data Translation with XSLT

2008-09-22T08:19:00Z

On previous post, we discussed creating a hierarchical XML from tabular data using only T-SQL.

On this post, we'll explore transforming tabular data in XML format to a hierarchical XML format using purely XSLT.

Let's say that we have an XML that is pretty tabular like the one below.

<?xml version="1.0" encoding="utf-8" ?>

<?xml-stylesheet type="text/xsl" href="[replace with the real stylesheet filename here].xsl" ?>

<Mappings>

    <Mapping>

        <Station>Science</Station>

        <Crew>Spock</Crew>

    </Mapping>

    <Mapping>

        <Station>Command</Station>

        <Crew>Kirk</Crew>

    </Mapping>

    <Mapping>

        <Station>Engineering</Station>

        <Crew>Scotty</Crew>

    </Mapping>

    <Mapping>

        <Station>Medical</Station>

        <Crew>Bones</Crew>

    </Mapping>

    <Mapping>

        <Station>Science</Station>

        <Crew>Ensign Disposable</Crew>

    </Mapping>

    <Mapping>

        <Station>Medical</Station>

        <Crew>Nurse Hatchet</Crew>

    </Mapping>

</Mappings>

Fig 1. Tabular Star Trek Crew to Station Mapping xml

And we want to transform it into something like:

<Mappings>

    <Station>

        <Name>Command</Name>

        <Crews>

            <Crew>Kirk</Crew>

        </Crews>

    </Station>

    <Station>

        <Name>Engineering</Name>

        <Crews>

            <Crew>Scotty</Crew>

        </Crews>

    </Station>

    <Station>

        <Name>Medical</Name>

        <Crews>

            <Crew>Bones</Crew>

            <Crew>Nurse Hatchet</Crew>

        </Crews>

    </Station>

    <Station>

        <Name>Science</Name>

        <Crews>

            <Crew>Ensign Disposable</Crew>

            <Crew>Spock</Crew>

        </Crews>

    </Station>

</Mappings>

Fig 2. Hierachical Star Trek Crew to Station Mapping xml

You can use the following XSLT to do it:

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                xmlns:ms="urn:schemas-microsoft-com:xslt"

                version="1.0">

    <xsl:output method="xml" indent="yes" />

    <!-- Store unique station names in variable -->

    <xsl:variable name="uniqueStations" select="//Mapping[not(Station=preceding-sibling::Mapping/Station)]/Station" />

    <xsl:template match="/">

        <Mappings>

            <xsl:call-template name="processStations" />

        </Mappings>

    </xsl:template>

    <xsl:template name="processStations">

        <xsl:for-each select="$uniqueStations">

            <xsl:sort select="." data-type="text" />

            <Station>

                <Name><xsl:value-of select="." /></Name>

                <xsl:variable name="currentStationCrews" select="//Mapping[Station=current()]/Crew" />

                <Crews>

                    <xsl:for-each select="$currentStationCrews">

                        <xsl:sort select="." />

                        <Crew><xsl:value-of select="current()" /></Crew>

                    </xsl:for-each>

                </Crews>

            </Station>

        </xsl:for-each>

    </xsl:template>

</xsl:stylesheet>

Fig 3. XSLT Needed for the Transformation

Frankly, I don't quite like this particular XSLT format since it is quite imperative in style (for each looping), but I couldn't find a more declarative template matching solution for this.

If anyone know a better solution, let me know.

One part that I like from this particular sample is by adding

<?xml-stylesheet type="text/xsl" href="[filename].xsl" ?>

to the xml file like in Fig. 1, you can test the transformation quickly just by invoking the xml file from a browser (Internet Explorer / FireFox will do this nicely). The only thing is that you can't really see the actual tag on screen. To quickly verify the resulting transformed XML, you can enter the following JavaScript code into the URL bar of your browser:

BLOCKED SCRIPTalert(document.documentElement.innerHTML)

Giving credit where credit is due... I used this ASP Alliance article by Teemu Keiski as base for this solution.

Share this post: | | | |

How to Make Yet Another Forum.Net to Work with Telligent Graffiti CMS

2008-09-21T16:51:00Z

In a recent project that I was handling, there was a requirement to have a forum on top of an existing Graffiti CMS. Instead of building one from scratch I decided to look for an open source .NET forum engine alternative. That's when I ran into Yet Another Forum.Net.

The task is simple enough. add a forum virtual directory and when someone click a link to http://blah/forum, YAF.NET should work.

After downloading the forum distributable and adding it as a Graffiti CMS virtual directory, some problems start showing up.

You can follow some steps described in the YAF.NET Installation Steps section in their Wiki to mitigate the problem but it doesn't get you 100% there.

Playing around with it, I managed to get it to work with the following steps:

- Get YAF.NET to work as a standalone application (setup db, etc.). If you can get it to work, you are about 90% there.
- Remove YAF.NET application from IIS Manager
- Add YAF.NET directory as a virtual directory of Graffiti or copy the entire YAF.NET directory to Graffiti root folder/forum. I.e. if you Graffiti CMS is installed in D:\Graffiti\Web, then copy YAF.NET content to D:\Graffiti\Web\Forum
- Copy all bin folder contents from YAF.NET directory to Graffiti bin folder
- Copy yafnet.config and urlrewriter.config to root directory of Graffiti
- Make sure to rename the web.config in YAF.NET directory back to default.config so it does not run
- In Graffiti CMS web.config, you need to make some modifications like:
- copy the configSection from default.config in YAF.NET directory across
- copy <yafnet ...> and <rewriter ...> from default.config across
- create a <location path="forum"> in Graffiti CMS web.config and copy the <system.web>...</system.web> from default.config into the <location...> tag.
- Make modification to <pages ...> tag in the newly copied part in the previous step. You need to copy <namespaces> and its content from Graffiti web.config and change <add namespace...> to <remove namespace...>

That's it. Test both Graffiti CMS and the newly integrated YAF.NET to make sure they work.

Attached is a sample of the modified Graffiti CMS web.config

Share this post: | | | |

100% Customized Development Effort vs Customization of Available Solutions

2008-09-21T16:48:21Z

Sometimes, and I am also guilty at this as well, we tend to want to develop a fully 100% customized solution from the very beginning, may it be an ASP.NET website or otherwise. After all, what fun is it in trying to customize someone else's code, right? Sometimes, it's just because we are not familiar with or don't have the time to look around / research what's out there that someone else has already built that might suit our project requirements.

Nowadays, I am of the opinion that spending your time looking around and trying to find out if there is such solution is really worthwhile and could really help you deliver the solution faster.

Of course, even if you do find the solution, you will still need to analyze if you need to further customize it or not. Perhaps the current solution will only cover 80% of your current requirements and you will need to spend time to fill in the 20% gap doing further customizations.

Sometimes such solution package is not too extensible, then you will need to consider what to do about the rest 20%. Do you build an 'out-of-bound' customized solution to fill in the gap? Can you hook it up to the lower level implementation of the current solution? i.e. go straight to the database and mess around with it? Those are risks that you will need to spend your time thinking about.

Another consideration that you need to take is how long will it take you and your team to figure out all the extensibility points of the current solution? Will it be longer than if you actually build up your own solution from scratch? In most cases, the answer will be no (time to customize an existing solution will not take longer than building one from scratch). But reflect on this point as well since you might find the answer is contrary to common thinking.

Just rambling on a thought that came into my mind during one of the projects that I'm doing. I hope this can be of use to you.

Happy analyzing :)

Share this post: | | | |

Debugging Adventure Fun Episode eerr... Whatever

2008-09-21T16:46:27Z

I was recently asked to debug a legacy Access 97 (YES, Access 97, lol) database frontend application that is connecting to a SQL Server 7 (Yes, SQL Server 7, lol) backend (Like I said... it's legacy, what do you expect ^_^). To make things worse, I had to debug this through a remote VNC session since the problem is occuring somewhere in Kalimantan and I am in Jakarta. What fun :), ... NOT! Tried debugging it on a local copy that I have (in VM of course, heck, I am not going to install Office 97 stuffs on my main machine, hehehe, although I did this once before when I didn't know better, hehehe. It did work however - side by side Office 97 and Office 2003 installation, haven't tried to do it with Office 2007, nor do I want to - btw, you have to do some tweaking here and there, but it worked. Uhm... Sorry for digressing... Back to laptop, as they said it...), but couldn't reproduce the error no matter what I do. The application ran just fine on the VM. So, the next logical step is to debug it where the problem is actually happening.

Anyhow, the problem presented to me was an error that kept showing up recently that stumped some unknown code module in the system (The error message provided is very generic like 'Unable to fullfil your request', apparently the person who created the application was well versed in security issue, you know... not exposing the internal system error to the client / user, but he forgot to log the real freaking error somewhere else for easy debugging...ARRGH. Oh, btw, if your Access application is hooked up to run some code automatically on load, you can launch it by holding down Shift key and double-clicking the .mdb file to stop the code from running. Sometimes this is necessary to allow you access to the object viewer, etc. Uhm... Back to laptop... (who the heck invented that phrase anyway...)

After setting numerous breakpoints and tracing the code from the startup form up to the point of failure (what a chore... stupid VNC won't allow me to press F8, argh, so I have to click on the VBA debug menu item one click at the time to step into / through some code). Oh, did I mentioned the sucky connection between Jakarta and our site in Kalimantan (in the middle of nowhere, literally in jungle, mining site)? Yeah, the VNC connection broke down on multiple occassions!! OMG. Speaking of hellish debugging session... Sigh. Ok, enough digressing... Back to laptop...

Erhmm... Where were we... oh yeah, after blah blah blah breakpoint, blah, and so on and what not... sigh... it turned out that the error was caused by a SQL Query that is constructed using string concatenation like:

"SELECT Field1, Field2 FROM SomeTable " + _
"WHERE Timestamp = '" + FormatMySillyDate(Now()) +"' " + _
"AND some other criteria here (not important)..."

And when the query got shoved to SQL Server through DAO, SQL just said huh? what date are you talking about... that's not a date!! and gave up.

Aside from the abysmal lacking of clear exception information available from the error handling routine that made debugging this application a hellish experience (oh and the VNC, and the network, etc. etc.), my gut instinct pointed me that this could be a localization issue. Since it worked perfectly on my VM (couldn't reproduce the error, remember?), it's clearly local to this one workstation (and it was confirmed by the operator that the one installed on the other computer is working just fine.) So... after confirming that the formatted date being passed is something like '18-Agust-2008', next step that I took is take a look at the Regional Settings of the computer and sure enough, it was set to Indonesian localization :). Changing this back to English (United States), fixed the problem.

After strongly warning the user about the "danger" of switching Regional Settings unilaterally and forwarded the issue and resolution to the support (help) desk team (just in case, in the future...), I finally could rest in peace, phew... (or if the boss is watching, rest in peace == back to working hard on the gazillions of other projects, simulteneously, and maintenance issues) There goes 2 hours of my life that I'll never get back.

Moral of the story is... you figure it out. I am beat (mentally) from debugging this... (^_^).

I apologize for rambling too much, but didn't you read my blog title? Hehehe.

p.s. Any exaggeration is purely for comical effect and not to be taken seriously. Funnier to read that way... Bah, why am I explaining this... Sigh.

Share this post: | | | |

Woot! We're Up Again.

2008-09-21T15:53:00Z

Apparently there was a database mishaps with the blog that was causing some downtime. I believe some of our community members spent their time volunteering at MS Indonesia and resurrected the database from the ashes. Hehehe. Great job guys.

It seems that I'm missing a couple of latest posts but at this point I'm just glad that most of the blog data were recoverable. It'd be a shame to lost all of them.

Anyhow, recalling back what I wrote from the recovered point. I believe I posted a question regarding where the Indonesian developer commuity stand on agile software development practices. I also posted something regarding how some of Vista GUI designs were very intuitive even for a 2.5 years old kid. And I might have lost a follow up post of my last xml post (the one on August 12th), but couldn't seem to recall what exactly I was posting about. Might still have a draft of it locally at work. I need to check if I can resurrect those.

Anyhow, now that we're back in business, let's start blogging again :)

Share this post: | | | |

Transforming Denormalized Tabular Data to Hierarchical XML Using SQL Server (2005)

2008-08-12T09:53:00Z

A friend of mine asked me today if there is any easy way to transform a table / query result that is quite tabular like the one in the table below to a hierarchical XML format just by using T-SQL query.

Id	Product	Activity
1	Product A	Activity A
2	Product A	Activity B
3	Product B	Activity C
4	Product C	Activity D

Fig 1. ProductMapping Table

<products>

    <product>

        <desc>Product A</desc>

        <activities>

            <activity>Activity A</activity>

            <activity>Activity B</activity>

        <activities>

    </product>

    <product>

        <desc>Product B</desc>

        <activities>

            <activity>Activity C</activity>

        <activities>

    </product>

    <product>

        <desc>Product C</desc>

        <activities>

            <activity>Activity D</activity>

        <activities>

    </product>

</products>

Fig 2. Expected XML Result

Although I dabble in XML, XPATH, XSLT and the like from .NET / JavaScript; I've never really touched SQL Server XML features that much so I decided to play around a little with it.

The only thing I remember about T-SQL and XML is that they have this FOR XML keyword that will allow you to transform table data to XML ... somehow.

After a bit of researching and trial and error, I came up with the following T-SQL Query:

SELECT

    DISTINCT product AS "desc",

        SELECT

            activity AS "*"

        FROM

            ProductMapping m2

        WHERE

            m2.product = m1.product

        FOR XML PATH('activity'), ROOT('activities')

FROM

    ProductMapping m1

FOR XML PATH('product'), ROOT('products')

Fig 3. First T-SQL attempt for transforming tabular data to XML

This gets me pretty close to what I want, but for some reasons the subquery result is HTML encoded.

<products>

    <product>

        <desc>Product A</desc>

        &lt;activities&gt;

            &lt;activity&gt;Activity A&lt;/activity&gt;

            &lt;activity&gt;Activity B&lt;/activity&gt;

        &lt;/activities&gt;

    </product>

    <product>

        <desc>Product B</desc>

        &lt;activities&gt;

            &lt;activity&gt;Activity C&lt;/activity&gt;

        &lt;/activities&gt;

    </product>

    <product>

        <desc>Product C</desc>

        &lt;activities&gt;

            &lt;activity&gt;Activity D&lt;/activity&gt;

        &lt;/activities&gt;

    </product>

</products>

Fig 4. Bad XML!!

After researching a little bit more and could not find a satisfactory and clean answer to do this, I finally just gave up and wrapped the above T-SQL in another SELECT statement with a bunch of replace functions surrounding it to transform all the < and > to actual < and >.

SELECT

    replace(

    replace(

        SELECT

            DISTINCT product AS "desc",

                SELECT

                    activity AS "*"

                FROM

                    ProductMapping m2

                WHERE

                    m2.product = m1.product

                FOR XML PATH('activity'), ROOT('activities'))

        FROM

            ProductMapping m1

        FOR XML PATH('product'), ROOT('products')

),

    N'&lt;', N'<'),

    N'&gt;', N'>') AS [Products XML]

Fig 5. This T-SQL gets me what I want but it's not really elegant.

Anyone know a better T-SQL only way to do this?

Share this post: | | | |