Jacob's Blog

I am currently on vacation after working non-stop for almost an year and I think I really needed a few days to relax. However, I found out again that “no-work is no-fun” and thought of building content an XML Training course that I always wanted.

It was really fun working on this and I did the first training with this course content at 4Axes InfoTech Services in Bangalore on 29th Dec 2009. It was a full day course that covered the XML support in SQL Server in detail.

You can find the details of the course content at http://beyondrelational.com/content/xmltraining.aspx

I have also setup an XML Resource Center where a lot of XML related information is posted. I have also setup an XML Forum and XML Group for people interested to participate in XML related discussions.

One of my friends emailed me this morning asking help for reading data from Google Analytics Atom feed, using Query with TSQL.

My friend sent me a sample XML document he obtained from Google Analytics and here is the query that reads information from it.

DECLARE @x XML
SELECT @x = '
<feed xmlns="http://www.w3.org/2005/Atom" 
    xmlns:dxp="http://schemas.google.com/analytics/2009" 
    xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/">
  <entry>
    <id>http://www.google.com/analytics/feeds/data?ids=ga:17042320&amp;
        ga:city=(not%20set)&amp;ga:country=Canada&amp;ga:date=20091204
        &amp;filters=ga:country%3D%3DCanada&amp;start-date=2009-12-04
        &amp;end-date=2009-12-18</id>
    <updated>2009-12-17T16:00:00.001-08:00</updated>
    <title type="text">ga:country=Canada | ga:city=(not set) | 
        ga:date=20091204</title>
    <link rel="alternate" 
        type="text/html" href="http://www.google.com/analytics" />
    <dxp:dimension name="ga:country" value="Canada" />
    <dxp:dimension name="ga:city" value="(not set)" />
    <dxp:dimension name="ga:date" value="20091204" />
    <dxp:metric confidenceInterval="0.0" name="ga:entrances" 
        type="integer" value="24" />
    <dxp:metric confidenceInterval="0.0" name="ga:exits" 
        type="integer" value="24" />
  </entry>
</feed>'

;WITH XMLNAMESPACES(
    DEFAULT 'http://www.w3.org/2005/Atom',
    'http://schemas.google.com/analytics/2009' as dxp
)
SELECT
    x.value('(dxp:dimension[@name="ga:country"]/@value)[1]','VARCHAR(20)') 
        AS country,
    x.value('(dxp:dimension[@name="ga:city"]/@value)[1]','VARCHAR(20)') 
        AS city
FROM @x.nodes('feed/entry') a(x)

/*
country              city
-------------------- --------------------
Canada               (not set)
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

I shared my PASS 2009 experience in the previous post. One topic that I did not mention in the previous post, and wanted to write separately is about the PASS Outstanding Volunteer Award.

Outstanding Volunteers

As many of you know, PASS is run by volunteers all over the world. These volunteers spend a significant part of their time and energy towards supporting the SQL Server community through PASS. All those volunteers are doing an Outstanding job for the benefit of every SQL Server professional, including me.

I started volunteering with PASS in early 2007. I am having a great time working with those Outstanding volunteers, which contributes significantly to my formation as an active volunteer in the PASS SQL Server community.

PASS Outstanding Volunteer Award

PASS recognizes volunteers with a very coveted PASS Outstanding Volunteer Award every year. I was very fortunate to get the award this year along with a few other PASS Volunteers during the PASS 2009 Summit in November 2009, Seattle, Washington.

Co-owners of this award

Though this award was given to me, there are a few Outstanding Volunteers who equally share the credit of this award. I got this award because of my efforts for PASS in the Asian Region. However, this was not a one-man-show. There is an exceptional PASS Asia Committee that worked very hard for growing PASS in the Asian Countries. This award belongs to everyone who contributed or worked with the committee in one way or another, especially Pinal Dave who worked very closely with me to achieve the goals and Sujata Mehta and Malathi Mahadevan from the Asian Regional Committee. We would not have been able to succeed with our efforts without the continuous guidance and support from Greg Low (PASS Director – Chapters), Rushabh Mehta (PASS Execute Vice President – and President Elect 2009) and Blythe Morrow (all-in-all?).

Other Outstanding Volunteer Award Recipients

Other volunteers who received the Outstanding Volunteer Award this year are the following:

1. Tim Ford
2. Grant Fritchey
3. Amy Lewis

The PASSion Award

The PASSion award is is PASS’s Top Honor which is described as “The PASSion Award is presented annually to a volunteer who demonstrates exceptional dedication to and enthusiasm for PASS and the SQL Server community. PASSion award recipients are nominated by PASS board members and volunteers and selected by the Board of Directors and the Volunteer Committee.” in PASS official website. This is the TOP honor any PASS volunteer can currently receive. This award was given to two exceptional volunteers this year:

1. Charley Hanania
2. Allen Kinsel

Thank You!

I would like to thank every one who directly or indirectly helped us to grow PASS in Asia. I look forward to meet the 2010 award recipients.