California accepts Open Source

A policy shift occurred January 7th with the California IT czar.

The CIO has issued a new policy directive that may make it easier for open source platform to compete for the scarce contract dollars available for IT projects.

Oracle and Microsoft dominating the California platform purchase pipeline may still be the norm. My hope is that with this new policy there is some opening of opportunity for good open source offerings and consulting.

Here is the link to the official PDF policy letter:
http://bit.ly/6XTifr

Simple XML query and looping

Working with a client's SQL 2005 stored procedure this week I came across a very complicated series of T-SQL. The jest of the procedure is to loop through a series of rows inside of an XML variable passed into the SP. The implmentation was built using OPENXML and Select statements with patterns. I spoke with the maintainers of the SP and it became clear that the functionality was overly complex and that using XML XQuery and XPath was a new skill set.

So I devised this simple XML sniplet that will run and demonstrates some of the basics available when dealing with XML data. This should click with most first time XML database folks accustomed to working with T-SQL. They can then explore the rich feature set available.

Dave W
--
-- Simple example of using XML query.
-- Build to shold some basics of "looping" through a set of XML "rows"
-- Dave Winters 6 Jan 2010
--
declare @x xml
set @x='
<grills franchise="1" regionname="West">
<location locationid="Sacramento">
<listing>MenuItem Pizza </listing>
<listing>MenuItem Salad </listing>
<listing>MenuItem Sushi </listing>
</location>
<location locationid="Auburn">
<listing>MenuItem french fries </listing>
<listing>MenuItem hamburgres </listing>
<listing>MenuItem coke </listing>
</location>
</grills>'
--
-- Get a specific location menu
--
SELECT @x.query('
for $listItem in /grills/location
where $listItem/@locationid="Auburn"
return $listItem
') as Result
--
-- XML fragments for the location ID attribute, not proper form.
-- To make it regular you'll need to put inside one root element
--
SELECT @x.query('
for $listItem in /grills/location
return <t>{$listItem/@locationid}</t>
')
--
-- Chunking some locations blobs
--
SELECT T.c.query('(.)[1]') as LocXML
FROM @x.nodes('/grills/location') T(c)
--
-- Build the master menu list
--
SELECT T.c.value('(./text())[1]', 'varchar(256)') as Master_Menu_List
FROM @x.nodes('/grills/location/listing') T(c)
--
-- XML is much more prowerful than this simple example.
-- This was to show how you can go through an XML object in a fashion similar to
-- processing records in SQL tables.
--