Stream validates EDI code

When using the EDISendPipeline in BTS2006 R2, that might be interesting:
My colleague Christian and me just found out that the EDI stream is validated by the stream reader while parsing the stream! So, when you work on the EDI stream which is produced by the EDI assembler, you get exceptions like "Error in serialization" if your EDI file is invalid. Quite clever how the programmers solved the validation of EDIFACT since it validates the file while returning it to the next step in the Send Pipeline.

Addition 2009-10-21:
Because of this, the EDI stream can't be read twice in following self-build pipeline components and BodyPart.Data.CanSeek is set the false.

Aggregating Values from a Hierarchy

In a session about the SQL Server 2008 datatype HierarchyID, the question came up whether you can easily ask question such as: "If I store things (and sup-things and so on) with a number in a hierachy, can I sum up the numbers of a thing, including the subthings?"
The answer is, yes, you can, and here is how it goes. The following code shows a table which hierarchically stores websites and their clickrate.

-- create hierarchical table
create table websiteCalls
(
path hierarchyid,
filename nvarchar(100),
clickCount int
)

-- insert root node "index"
insert into websiteCalls values (hierarchyid::GetRoot(), 'index.html', 1000)
go

-- insert a level 1 entry "products"
declare @index hierarchyid
select @index = path from websiteCalls where filename='index.html'
insert into websiteCalls values (@index.GetDescendant(null, null), 'products.html', 200)

-- insert two more level 1 entries "customers" and "impressum"
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
insert into websiteCalls values (@index.GetDescendant(@products, null), 'customers.html', 100)
insert into websiteCalls values (@index.GetDescendant(null, @products), 'impressum.html', 10)

-- insert a "product1" on level 2
insert into websiteCalls values (@products.GetDescendant(null, null), 'product1.html', 300)
declare @product1 hierarchyid
select @product1 = path from websiteCalls where filename='product1.html'

-- insert two more level 2 entries
insert into websiteCalls values (@products.GetDescendant(@product1, null), 'product2.html', 50)
insert into websiteCalls values (@products.GetDescendant(null, @product1), 'product0.html', 100)
go

-- get all websites and their clickrates
select path.ToString(), filename, clickCount from websiteCalls

-- get the sum of clickrate number of all product-pages:
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
select SUM(clickcount) from websiteCalls where path.IsDescendantOf(@products) = 1 and path<>@products