Neal Walters - Mostly Biztalk Blogs

Monday, October 26, 2009

Example of an SQL Query (in a view) that uses XPath to report on XML elements in an XML column:

CREATE VIEW [dbo].[ViewEventLog]

AS SELECT id, eventDateTime,

CAST(TMXML.query('//Source/requestor/text()') AS varchar(50)) AS requestor,

CAST(TMXML.query('//Source/requestType/text()') AS varchar(50)) AS requestorType,

CAST(TMXML.query('//Source/objectId/text()') AS varchar(40)) AS objectId,

CAST(TMXML.query('//Source/objectType/text()') AS varchar(24)) AS objectType,

CAST(TMXML.query('//Source/transactionGuid/text()') AS varchar(40)) AS transactionGuid,

CAST(TMXML.query('//Source/dateTimeReceived/text()') AS varchar(40)) AS dateTimeReceived,

CAST(TMXML.query('//Source/isResponseExpected/text()') AS varchar(5)) AS isResponseExpected,

CAST(TMXML.query('//policyName/text()') AS varchar(24)) AS policyName,

CAST(TMXML.query('//*[local-name()="Itinerary"]/status/text()') AS varchar(24)) AS itineraryStatus,

CAST(TMXML.query('//currentStepName/text()') AS varchar(24)) AS currentStepName,

CAST(TMXML.query('//currentStepStatus/text()') AS varchar(24)) AS currentStepStatus,

CAST(TMXML.query('//currentStepIndex/text()') AS varchar(10)) AS currentStepIndex,

CAST(TMXML.query('//currentErrorType/text()') AS varchar(24)) AS currentStepErrorType,

CAST(TMXML.query('//currentProcessCode/text()') AS varchar(40)) AS currentStepProcessCode,

CAST(TMXML.query('//*[local-name()="Exceptions"]/*[local-name()="Exception"][1]/messageText/text()') AS varchar(MAX)) AS exceptionMessageText,

CAST(TMXML.query('//*[local-name()="Exceptions"]/*[local-name()="Exception"][1]/source/text()') AS varchar(60)) AS exceptionSource,

CAST(TMXML.query('//*[local-name()="Exceptions"]/*[local-name()="Exception"][1]/stackTrace/text()') AS varchar(MAX)) AS exceptionStatckTrace,

CAST(TMXML.query('count(//*[local-name()="TMXML" and namespace-uri()="http://TMAC.BizTalk.ESB.TMXML.Schemas.TMXML_1_0"]/*[local-name()="Messaging" and namespace-uri()="http://TMAC.ESB.BizTalk.Schemas.Canonicals.Messaging_1_0"]/*[local-name()="Loans" and namespace-uri()="http://TMAC.BizTalk.ESB.TMXML.Canonicals.Loans_1_0"]/*[local-name()="Loan" and namespace-uri()="http://TMAC.BizTalk.ESB.TMXML.Canonicals.Loan_1_0"])') AS varchar(6)) AS countLoans

FROM dbo.Event

The table was created something like this:

CREATE TABLE [dbo].[Event]
( [id] [int] IDENTITY(1,1) NOT NULL,

[eventDateTime] [datetime] NOT NULL,

[Requestor] [varchar](50) NULL,

[RequestType] [varchar](60) NULL,


Note that TMXML is defined as an XML column (with no schema specified).

Friday, September 25, 2009

Ray Kurzweil, author of “The Singularity” forecasts geometric growth in knowledge and technology to a point in approximately 2045, beyond which we are unable to see. Consider for example, my first computer had a 10 megabyte hard-drive that cost about $250. Today, I have at least a 4 gigabyte flash drive hanging from my keychain. That’s an increase in 400 times the disk space, and a vast decrease in the cost and physical footprint in about 20-25 years. Most cell phones have more computing power than the computers that flew the Apollo spaceship to the moon.

How does this apply to the IT world. In about 2000, I got my first Microsoft certifications. To be a Microsoft Certified Solution Developer (MCSD) back then, you basically had to study VB6 (with COM), SQL7, what is now called “Classic ASP 3.0” (Visual Interdev including HTML and JavaScript), and Solutions Architecture.

Nine years later, the most comprehensive certification for a developer is the (MCDP-Enterprise Application Developer). This includes 6-exams, either on C# or VB.NET, including .NET Framework, Windows Forms Applications, ASP.NET Applications, Windows Communication Foundations Applications (WCF), ADO.NET Applications, and a “PRO” exam. Notice that knowledge of SQL was replaced by ADO.NET, an abstraction layer that allows developer to work with any data source. The ADO.NET exam covers the new LINQ technology. . Even though employers typically require detailed knowledge of either MS/SQL Server or Oracle, Microsoft no longer tests these skills for the developer certifications.

Interesting enough, a Microsoft “Certified” developer is not required to have any knowledge of BizTalk or SharePoint (there are 4 exams on SharePoint and 2 on BizTalk). Next year, 2010 introduces SharePoint 2010, Visual Studio 2010, and probably “OSLO” and the new “M”-Modeling language. It’s currently Sept 2009, and I’m updating many of my old .NET certifications the VS2008/.NET 3.5 platform.

In the world outside of Microsoft, language, frameworks, and platforms keep popping up left-and-right. In the last year, I have made use of Google App Engine, Python, Dojo/Dijit, and JQuery. Ruby on Rails seems to be quite popular this past year. Open Source systems and add-on/plug-ins are proliferating like rabbits.

I have one book on LINQ alone which is 630 pages, which account for probably 20% of just one exam (the ADO.NET above). Back in 2000, you could probably study three or four books at about 1000 pages each. My original VB6 books was 1200 pages, and that covered windows, COM, and distributed applications (two exams). Today, my guess is that the above exams will require 6-10 books of 1000 pages each. And again, that is totally ignoring Sharepoint and BizTalk. My BizTalk library takes up about a foot of shelf space, and my SharePoint library takes up about a foot and a half. A few months ago, I threw away about two shelves of books to make room for the “new stuff”. I figured .NET 1.0 books were useless, now that we are on .NET 3.5. SQL 7 books had to go when we are now on SQL 2008.

As an aside, I have found that Microsoft offers premium “Master” Certifications (formerly the “ranger” program) in SharePoint, SQL, Exchange Server, Office Server, and Windows Server, but nothing there for BizTalk or developers.

Kurzweil and others estimate that knowledge doubles about every year. This means that to keep up, if you have “x” knowledge now, you will need 8x knowledge 3 years from now. OR – you will have to specialize. You will have to go wide(and thin) or narrow (and deep), be a generalist or a specialist. If you have seen lectures on TED ( you need to check them out. Alain de Botton gave a talk entitled “A kinder, gentler philosophy of success”. The gist of what he said was that success in one area comes with lack of success in other areas. For example, to be the world’s greatest pianist, you probably won’t be a great C# coder.

Tuesday, July 28, 2009

Google provides a "gdata" interface for you to access Google Documents via code.

There are a few points that I missed in their documentation:
1) The domain to be used in the URL of an atom feed is just
If you try you get a 302 redirect and actually the system throws an error. They seem to be expecting well-formed XML back from the atom feed, and rather than seeing the 302 redirect error, you get a nasty message like this:

in GDataFeedFromString
return atom.CreateClassFromXMLString(GDataFeed, xml_string)
File "c:\Program Files\Google\google_appengine\atom\",
line 97, in
tree = ElementTree.fromstring(xml_string)
File "", line 85, in XML
SyntaxError: not well-formed (invalid token): line 154, column 60

The only way I found the 302 redirect message was by using the Eclipse/PyDev debugger, and poking around at the string that came back from the http GET command.

2) When they ask for a docId in a feed, it is NOT the document name/title.
The document id looks something like a small GUID, for example "ddjjzvdp_905w5v63gp", and it goes on the URL as shown below.

url =

3) Thus, you have to do a lookup of your filename to find the docId.

q =
q['title'] = '3WCTest' #this is the name (title) of the filename you want to find
q['title-exact'] = 'true'
feed = client.Query(q.ToUri())
if len(feed.entry) > 0:
docId = feed.entry[0].resourceId.text
print "docId=" + docId

NOTE: If you haven't seen Python before, indentation is critical. All statement under an "if" statement for example, must be indented at the same level.

domain = ""
exportFormat = "html"
url = "http://" + domain + "/feeds/download/documents/Export?docID=" + docId + "&exportFormat=" + exportFormat

print "url = " + str(url)
result = client.Get(url,converter=str)
print "---- Results: ------- "
print "Google GDocs Response 2: " + result

To get the client object, the following code is used before the above two code snippets:

client =
#Include this next line if you are running under GAE
# (Google App Engine)
gdata.alt.appengine.run_on_appengine(client, store_tokens=False, single_user_mode=True) = 'your-user'
client.password = 'your-email'
client.service = 'writely' #for Google Docs
#client.accountType = "HOSTED_OR_GOOGLE"
client.accountType = "HOSTED"

For more info, see google forum post:

I've spent the last few months working on Google App Engine (GAE). Yes, it's a big change from my normal work with BizTalk. I'm writing Python Web applications that interact with Google's BigTable datastore.

With Google App Engine, you can currently write code in Python or Java. You download the GAE SDK (software development kit) to your local machine, and you can run a test server. Basically they have a single-threaded web server that runs in a command prompt, and you access it by using http://localhost:8080/YourApp (or any other port number you specify).

After you have a working/tested application, you can upload it to run on You create an account, such as, and of course you can use redirection to route your domain there.

Google gives a daily quota of CPU and disk space that is free. So far, in my testing, I've only used a max of 2% of the quota in a day, so the cost of hosting the website so far is free. If my website gets 1000s of users, it will automatically scale on Google's infrastructure, and at some point, I will get a monthly bill for additional resources.

BigTable is a non-relational, non-SQL database. It still has traditional "tables" with rows/columns. You can still have one-to-many and many-to-many relationships, but no "join" commands. Everything is exposed as Python (or Java) classes and lists. I'll have to elaborate on that in a future blog. The main benefit of BigTable is its scalability. There are three downsides to BigTable: 1) limit of 1000 rows returned from any query, 2) sometimes you have to write more code than you would with SQL, 3) there are no aggregate commands (SUM/COUNT/AVG/MIN/MAX...). Thus, the database is not designed for business intelligence and historical reporting. If you do a lot of work, trying to do for example multiple calls and your own SUM or COUNT, you tend to eat up more of your CPU quota.

P.S. I'm looking for a new contract now (July/Aug 2009), as either Senior Biztalk Architect/Developer or a Google App Engine consultant/developer. My contact info is available on

Tuesday, June 10, 2008

If you are getting this error:

The "succeeded" operator can only be applied to an immediate child scope or service,
then make sure you specify a valid transactionName in parentheses, for example:


Note: SendLoanData is not the name of the scope, it is the TransactionIdentifier that you assign to the scope (which you could then use the same text value as the scope name).

This error was related to the compiler error:
"must receive before sending a fault message on an implemented port".

The issue here has to do with publishing an orchestration as a web service. Typically you have a two-way receive port, where you receive a message, and everything works fine, you send a response. But sometimes, you must also add a Fault response to the receive port.

This has already been discussed in the two blogs below
(more references for more info about the "succeeded" operator in BizTalk).,guid,abdd6ca2-e964-437d-8bee-0188ec5b5afa.aspx#commentstart

Tuesday, May 20, 2008

Another nice blog: SQL Statements that tell you why a Deploy won't work, i.e. what maps or pipelines may be used on ports (Eric Stott's blog):

I found this blog from Richard Seroter called "BizTalk Code Review Checklist":

Sunday, April 06, 2008

Earlier this year, I released a new training to learn MySQL.
It uses the open-source Joomla database as an example, and teaches the basics of SELECT, INSERT, UPDATE, DELETE, along with how to do backups/restores.

Why I recently attended Intalio BPMN class - see my new blog at M2MSys:,Why-BizTalk-Guru-Attended-Intalio-BPMN-Class-.html

Friday, December 14, 2007

I recently created a new set of Joomla Video Tutorials called "Joomla Unmasked". This is a follow-up course to our prior course called Joomla Magic.

PACKT Publishing just conducted a contest and named Joomla as Best PHP Open Source Content Management System

Joomla can be categorized as a CMS, also known as a Content Management System. SharePoint is Microsoft's offering of a CMS (but it is also of course a document storage system). I have found that PHP-Fusion much easier to use than Joomla, however Joomla seems to be gaining wider recognition and acceptance. While PHP-Fusion has maybe 60-80 mods (plug-ins), Joomla has something like 2500 extensions (both free and paid).

Both PHP-Fusion and Joomla are "Open Source", meaning that you can download the software for FREE, and even view, learn-from, or change the source code to your heart's content.

NOTE: I haven't blogged on BizTalk for the last year due to a recent contract that didn't want me to "leak" our supposed secrets. I have, however, started to do book-reviews and/or book-summaries of recent books that I have read on my Neal Walters personal site.