Monthly Archives: September 2009

xQuery and SQL Server – a Basic Look

The database table which I am using in this post: xmlDB.zip

xQuery comes in quite handy when it comes to querying Xml fields in SQL Server 2005 or 2008. SQL Server supports many xQuery language features, and its Xml data type provides a variety of functions to enable the use of xQuery.

To demonstrate, I wanted to keep things very simple and created a relation with the following schema:

schema
The ItemId is just an artificial identifier which performs the role of primary key for each tuple. The SalesRep field (which is of the Xml type) contains information about the sales reps. The Xml fragment stored in that field is similar to the ones which I have been querying in previous posts.

Starting off with a simple example, I have used the query function of the Xml type to return the Name element from the SalesRep field for each tuple:

SELECT SalesRep.query('//Name') as [RepName]
FROM Employees

which returns:
repName
To return the actual text nodes of particular Name elements, we can use:

SELECT SalesRep.query('//Name/child::text()') as [RepName]
FROM Employees
WHERE Office = 'Brisbane'

If we want to use an xQuery query in a WHERE clause, we need to use the exist function of SQL Server’s Xml type. For example, we may want to return every REP element with an ID of 8:

SELECT SalesRep.query('/REP') as [Rep]
FROM Employees
WHERE SalesRep.exist('/REP/ID/child::text()[. = "8"]') = 1

The way we do that, is to use a predicate in the WHERE clause. The query has asked for every element that is returned when the predicate is true i.e. when the exist function equals 1 (1 being true).

We may want to find those sales reps whose Sales were less than $10,000:

SELECT SalesRep.query('/REP') as Rep
FROM Employees
WHERE SalesRep.exist('/REP/Sales/child::text()[xs:integer(.) < 10000]') = 1

This is just a taste of what can be done in terms of utilizing xQuery to query XML fields in SQL Server databases. Such queries are much faster, and more efficient, than ones which cast the Xml field to NVARCHAR and then perform a LIKE %xyz% in the WHERE clause. Cool, eh?!

Rounded Corners on .NET GDI+ Rectangle

Recently, when I was working through a WF hands-on-lab, I came across a really cool method which adds rounded corners to a GDI+ rendered rectangle:

        private GraphicsPath RoundedRect(Rectangle frame)
        {
            GraphicsPath path = new System.Drawing.Drawing2D.GraphicsPath();
            int radius = 7;
            int diameter = radius * 2;

            Rectangle arc = new Rectangle(frame.Left, frame.Top, diameter, diameter);

            path.AddArc(arc, 180, 90);

            arc.X = frame.Right - diameter;
            path.AddArc(arc, 270, 90);

            arc.Y = frame.Bottom - diameter;
            path.AddArc(arc, 0, 90);

            arc.X = frame.Left;
            path.AddArc(arc, 90, 90);

            path.CloseFigure();

            return path;
        }

I have seen solutions around the Web for this which are much longer and involved. I really liked this because it is simple, and just a few lines of code. It is probably not as extensible as some other solutions. But if you just need a simple way to round those corners, this method fits the bill.
To use it, just override the onPaint event of your form:

        protected override void OnPaint(PaintEventArgs e)
        {
            base.OnPaint(e);   
            Rectangle frameRect = new Rectangle(10, 10, 400, 50);

            SolidBrush fillFrame = new SolidBrush(Color.CadetBlue);

            Pen outline = new Pen(Color.Black, 2.0F);
            e.Graphics.DrawPath(outline, RoundedRect(frameRect));
            e.Graphics.FillPath(fillFrame, RoundedRect(frameRect));
        }

That will render the rectangle depicted below:rectangle with rounded corners

xQuery and Recursion

In my last post, I gave a couple of examples of writing and calling functions. In this post, I want to look at how to solve a problem with recursion. Again, I will work with the same XML document from my previous post. Here, I will look at how to format the sales numbers as currency values.

For the sake of simplicity, I will not deal with cent values. So, no decimals. This is just so that we don’t lose focus from the main objective of the exercise.

Formatting a currency, using the Australian region style, uses commas as thousand separators i.e. $10,000.

So, my first pass at solving this problem would be a function such as:

declare function dave:formatCurrencyLimited($dollarAmt as xs:string) as xs:string
{
	let $nrDigits := string-length($dollarAmt)
	return 
		concat("$", concat(substring($dollarAmt, 1, $nrDigits - 3), concat(",", substring($dollarAmt, $nrDigits - 2))))
};

You can see that I have used the built-in, string-formatting functions concat , substring and replace to return values in the currency format that we want.

Using that in the following xquery:

let $reps := doc("reps.xml")/SampleDataset
let $sales := $reps//Sales
return
	<Result>
		{
			for $eachSalesNr in $sales 
			return 
				<SalesAmount>{dave:formatCurrencyLimited($eachSalesNr/child::text())}</SalesAmount>
				
		}
	</Result>

would return the following result:

<?xml version="1.0" encoding="UTF-8"?>
<Result>
   <SalesAmount>$10,440</SalesAmount>
   <SalesAmount>$17,772</SalesAmount>
   <SalesAmount>$23,880</SalesAmount>
   <SalesAmount>$7,663</SalesAmount>
   <SalesAmount>$21,773</SalesAmount>
   <SalesAmount>$32,294</SalesAmount>
   <SalesAmount>$11,983</SalesAmount>
   <SalesAmount>$14,991</SalesAmount>
</Result>

Now that works fine when the values are less than 1,000,000. But for any value above 999,999, my function would only return a string with one comma e.g. 1000,000. Not knowing how big the potential sales could be, we can’t really get the job done properly with my 1st attempt.

To solve this problem, we can use recursion. If you are unfamiliar with recursion, you should read about it elsewhere, and then come back to see what I have done with the following function:

declare function dave:formatCurrency($dollarAmt as xs:string) as xs:string
{
	let $nrDigits := string-length($dollarAmt)
	return 
		if($nrDigits > 3)
		then concat(dave:formatCurrency(substring($dollarAmt, 1, $nrDigits - 3)), replace($dollarAmt, $dollarAmt, concat(",", substring($dollarAmt, $nrDigits - 2))))
		else concat("$", $dollarAmt)
};

Looking at that version of the function, we can see that the base case is satisfied where $nrDigits equals 3 or less. If we have not reached the base case, the function calls itself until the base case is reached (it reduces the xs:string parameter by 3 each time, until the base case is satisfied).

Now, if I change Aaron’s sales amount to 10440000, my new function will return the following result:

<?xml version="1.0" encoding="UTF-8"?>
<Result>
   <SalesAmount>$10,440,000</SalesAmount>
   <SalesAmount>$17,772</SalesAmount>
   <SalesAmount>$23,880</SalesAmount>
   <SalesAmount>$7,663</SalesAmount>
   <SalesAmount>$21,773</SalesAmount>
   <SalesAmount>$32,294</SalesAmount>
   <SalesAmount>$11,983</SalesAmount>
   <SalesAmount>$14,991</SalesAmount>
</Result>

And this function will work every time, regardless of how big the sales figure becomes.

xQuery Functions

xQuery supports functions. In this post, by way of an example, I will demonstrate how to go about writing xQuery functions. For the example, I will be querying the following XML document (simple data about sales representatives):

<?xml version="1.0" standalone="yes"?>
<SampleDataset>
  <REPS>
    <ID>1</ID>
    <Name>Aaron</Name>
    <RegionID>1</RegionID>
    <Sales>10440</Sales>
  </REPS>
  <REPS>
    <ID>2</ID>
    <Name>Larry</Name>
    <RegionID>2</RegionID>
    <Sales>17772</Sales>
  </REPS>
  <REPS>
    <ID>3</ID>
    <Name>Andrew</Name>
    <RegionID>3</RegionID>
    <Sales>23880</Sales>
  </REPS>
  <REPS>
    <ID>4</ID>
    <Name>Mary</Name>
    <RegionID>1</RegionID>
    <Sales>7663</Sales>
  </REPS>
  <REPS>
    <ID>5</ID>
    <Name>Sally</Name>
    <RegionID>4</RegionID>
    <Sales>21773</Sales>
  </REPS>
  <REPS>
    <ID>6</ID>
    <Name>Nguyen</Name>
    <RegionID>2</RegionID>
    <Sales>32294</Sales>
  </REPS>
  <REPS>
    <ID>7</ID>
    <Name>Francis</Name>
    <RegionID>4</RegionID>
    <Sales>11983</Sales>
  </REPS>
  <REPS>
    <ID>8</ID>
    <Name>Jerry</Name>
    <RegionID>3</RegionID>
    <Sales>14991</Sales>
  </REPS>
</SampleDataset>

Let’s say we want to find out the average sales amount, along with the details of those reps who have achieved sales above that average amount. First, we need to calculate the average amount of sales. To achieve that, we can write the following function getAverageSales:

declare namespace dave = "http://www.localhost:8090/xquery";

declare function dave:getAverageSales($allReps as element()) as xs:decimal
{
	let $avgSales := xs:decimal(avg($allReps/REPS/Sales))
	return $avgSales
};

A couple of things to notice.

  • I have created a namespace for the function – dave.
  • the function takes one parameter, being an element. The element which will be passed to this function will be the SampleDataset element i.e. the root element of the XML document.
  • the function has an xs:decimal return-type.
  • the function uses the built-in function, avg, on the sequence of nodes comprising the Sales. It then casts the result of that to the xs:decimal type, using the xs:decimal constructor. That amount is returned.

I haven’t really added much value to the avg function there. But I wanted to start with a simple example.

Next, we need a function that will take as parameters:

  • a sequence of elements for the sales representatives; and
  • the average sales amount.

This function will return a sequence of elements representing those sales representatives who achieved sales greater than the average. This can be written as follows:

declare function dave:getEmployeesBeatAvg($allReps as element()+, $avgSales as xs:decimal) as element()+
{
	let $empsBeatAvg := $allReps[Sales > $avgSales]
	return $empsBeatAvg
};

Finally, we just need to write the query itself. Calling the functions is much like how it is done in other languages. You call the function and pass it actual parameters:

let $reps := doc("reps.xml")/SampleDataset
let $avgSales := dave:getAverageSales($reps)
let $repsBeatAvg := dave:getEmployeesBeatAvg($reps/REPS, $avgSales)
return
	<Result>
		{
			<AvergageSales>{$avgSales}</AvergageSales>,
			<EmployeesAboveAverage>
				{
					for $eachRep in $repsBeatAvg
					order by $eachRep/Sales descending
					return
						<Employee>{$eachRep/ID, $eachRep/Name, $eachRep/RegionID, $eachRep/Sales}</Employee>
				}
			</EmployeesAboveAverage>
		}
	</Result>

This will give us the following result:

<?xml version="1.0" encoding="UTF-8"?>
<Result>
   <AvergageSales>17599.5</AvergageSales>
   <EmployeesAboveAverage>
      <Employee>
         <ID>6</ID>
         <Name>Nguyen</Name>
         <RegionID>2</RegionID>
         <Sales>32294</Sales>
      </Employee>
      <Employee>
         <ID>3</ID>
         <Name>Andrew</Name>
         <RegionID>3</RegionID>
         <Sales>23880</Sales>
      </Employee>
      <Employee>
         <ID>5</ID>
         <Name>Sally</Name>
         <RegionID>4</RegionID>
         <Sales>21773</Sales>
      </Employee>
      <Employee>
         <ID>2</ID>
         <Name>Larry</Name>
         <RegionID>2</RegionID>
         <Sales>17772</Sales>
      </Employee>
   </EmployeesAboveAverage>
</Result>

In my next post about xQuery, I will take a look at how we can take those amounts and format them as currency.