Output SQL Data to XML easily

So today I needed to output data to XML at my job and rather than going the traditional way of creating a ColdFusion file to query and output the data I decided to see if I could just do it in MSSQL. Well, appears there is by using the "for XML" clause. There is a lot to learn from it and the docs are available in the following links.

• SQL Server 2000 XML
• What's New in FOR XML for SQL 2005
• What's New in FOR XML for SQL 2008 White Paper
  I think this white-paper explains the difference between SQL versions the best.

What I will do here is just explain some of the basics so you can go ahead and do as you please from here on. For my examples I will use the AdventureWorks default database but you can easily modify the SQL I have to your own and play with the results.

There are several ways to get XML output based on the XML Mode used, the following are available.

• RAW
  The RAW mode takes the query result and transforms each row in the result set into an XML element with a generic identifier row as the element tag and the columns in the SELECT as attributes.
• AUTO The AUTO mode returns query results in a simple, nested XML tree.
• EXPLICIT (I DO NOT DEMO THIS ONE)
  In EXPLICIT mode, you can explicitly define the shape of the resulting XML tree. Using this mode requires that the queries be written in a specific way, so that additional information about the desired nesting is specified explicitly as part of the query. The reason I don't demo this one is because of the new PATH mode in SQL 2005+, if you are still in 2000 and need to write complex XML docs then I suggest you look up this mode.
• PATH (SQL 2005+)
  A simple way to generate complex XML documents rather than explicit.

The examples are as follows

• Using for XML AUTO
• Using for XML PATH
• Using for XML PATH('path'),ROOT('root')
• Using for XML PATH('path'),ROOT('root') and making the contactID column an attribute
• Using for XML RAW('path'),ROOT('root')

---

Using for XML AUTO Returns this

Back to top

---

Using for XML PATH Returns this

Back to top

---

Using for XML PATH('path'),ROOT('root')
Here we add a root node and a cutom name for each row by defining the path value. Returns this

Back to top

---

Using for XML PATH('path'),ROOT('root') and making the contactID column an attribute
Here we add a root node and a custom name for each row by defining the path value. Returns this

Back to top

---

Using for XML RAW('path'),ROOT('root'), ELEMENTS XSINIL
This is the same as PATH it just returns the parent node with the XML Namespace. The ELEMENTS directive provides an XSINIL option to map NULL values to an element with an attribute xsi:nil="true". Returns this

Back to top

In conclusion

When working with this data you can either render your results to text and/or grid. I prefered grid because the results are essentially a link that opens the xml document which then you can either copy and paste or save to your hard drive.

Some books to consider ....

Beginning SQL Server 2008 for Developers: From Novice to Professional
SQL Server 2008, Developer Edition
Murach's SQL Server 2008 for Developers (Murach: Training & Reference)

Maximixe Google Chrome on a Mac

So today I was playing with Google Chrome on my mac and have decided to make it my default browser for "Browsing the Internet". I say that because I will still use Firefox as my main browser for "Development Debugging" and of course like any other web developer I am forced to continue to support IE 6+, so for that I use Parallels and test on my VM.

Anyways, enough for the introduction to this post. Basically one thing I did not like about Google Chrome was that I could not maximize the window to my full screen size, well at least until now. I first tried using the default Mac option of clicking the green button on the window with the option key held down but that didn't work. All that did was maximize the window to the content within. So what is the answer you may ask?

Shift+Green Button

Yeap that is it, that will maximize your window to your full screen size. If this post was useful, let me know by leaving me a comment below.

Woo Hoo!!! I got Google Voice!!!!

So as the tile of my post says, I finally got my Google Voice invitation. You will notice now a "Call Me" widget on the right hand side of my blog. So go ahead and call me and leave me a message if you need to get in touch with me. I've opted to send it directly to voice mail for now but from time to time I may be picking it up.

Easy Code Syntax in Blog.

In order to prepare to write code on this blog, I decided to go ahead and see how the code syntax plugins worked and I found one called SyntaxHighlighter by Alex Gorbatchev. So this is my test post. The following post by Carter Cole explains how to install and use.

Jumsoft Products and Coupon Code

Since it is the beginning of the new year I was set on finding a finance application to use on my mac. I've been a Microsoft Money user for over 5 years now and it was one of the last applications that I hung on to as a PC user. This past year I read somewhere that MS will no longer continue to develop and eventually support that application so I thought to myself if there was a better time to move it would be now, so my search began.

I checked a couple of applications and I just did not like the UI or the price was ridiculous until I ran into Money3 by Jumsoft. They got it right, at least in my opinion. The UI is great, the price is great and the accompanying iPhone app ... well great for my needs. There are a couple of things I wish it could do or maybe I just haven't played with it enough but they are as follows.

• Check Printing
• Apply payments to invoices as deposits to accounts

That's about it and as far as the check printing, a rep told me that there is a big update coming soon and that will most likely be part of it.

Anyways, enough of that, I think they have great products that any person using a mac should take a look at, so go ahead and check them out by clicking here. Now the little extra that I found out today by playing on their store. Pick any product(s) and in the coupon code field in your cart, type "APPLE" and you will get 50% off your purchase. Now I do not guarantee this or how long it will last but it was a discovery I made so here I am to share with you. Hopefully someone found this helpful and if so let me know.

Learning Objective-C and getting back to this ....

Ok, so as anyone who has landed here before already knows I don't post much, but one of my New Year Resolutions is to be a little more proactive with what I do. First a little update on me. I still have Fuse Developments up an running but at the same time I am the Senior ColdFusion Developer at Jobsite123.com LLC. I currently work with a great team of guys and I am learning new things everyday.

Finally after all these years I am using the ColdBox Framework, even though I was at Sandals back in the day when Luis and his colleagues decided to create this amazing framework I never got around to actually using it. Now that I have, I don't see myself doing any new projects without it. Also, I've noticed a lot of CF Bloggers love to use JQuery but I am a Prototype user by heart and I will make my best effort to write some tutorials about using this great JavaScript Framework along with it's companion animation suite, Scriptaculous.

One of the main reasons I don't blog much is my terrible grammar skills, which many of you will eventually experience. Maybe this will help me become a better writer/communicator ... we'll see. Also, I am self taught and sometimes I think I don't have much to offer but since my new job, I've realized that everyone always has something to offer and here goes my attempt.

So to summarize this is what I do, what I am learning and what I will be posting about in the near future.

• ColdFusion
  Expect version 8 and 9 tutorials and goodies.
• JavaScript
  Expect tutorials on using Prototype and Scriptaculous.
• CSSExpect tutorials on using css to create better layouts and leaving those nasty nested tables behind.
• Objective-C and Cocoa (Cocoa Touch)Yes after being a member for over a year I am finally taking the time to learn how to create Mac and iPhone applications and I may post things that I learn here.

That's pretty much it .... ohh and Happy New Year!!!!!