I recently came across a newsgroup post discussing distinguishing fields in an auto-generated SQL Adapter schema, and after a bit of investigation, came up with a way to easily distinguish schema records.
Now Jan Eliasen gave a perfectly good response to the newsgroup post, and helpfully pointed to his blog post on how to flip the default “records” to “elements” for easier manipulation.
This however got me thinking as to whether the restriction on distinguishing record types was a tool limitation, or compiler/engine related. If you try to distinguish a record type, the “Promoted Properties” window doesn’t enable the “Add” button. Given that a “record” is really just an XSD element, and that often auto-generated schemas build all the nodes as records, this limitation sometimes screws you. So, I opened my XSD schema in the VS.NET XML Editor instead of the BizTalk Editor.
I then manually added a new “distinguished field” to the “properties” collection of the schema. After saving, and then opening the schema once more in the BizTalk Editor, voila, it now shows up as a distinguished field in the “Promoted Properties” window.
To prove that this isn’t some sort of trickery, I then processed a message through the BizTalk engine, stopped by send port, and observed the context properties of my message. Sure enough, my “record” was properly distinguished and accessible.
I got a little frisky and wondered if I could also solve the age-old problem of distinguishing repeating nodes. The Editor tool prevents this activity because there’s no way to designate which index of the repeating node you want. The standard solution is to promote/distinguish in an inbound pipeline instead. However, what if you KNEW that you only wanted the first repeating node as the distinguished value? Could you also manually add this distinguished field to the schema?
Alas, despite numerous varieties of syntax, I couldn’t get the compiler to approve of this. I consistently got the compile time error saying The promoted property field or one of its parents has Max Occurs greater than 1. Only nodes that are guaranteed to be unique can be promoted as property fields.. I tried using “position()=1” or a “[1]” indexer, and either way, I struck out.
But, at least now I have a simple way to distinguish records, so it’s not a total loss.
Technorati Tags: BizTalk
Richard Seroter's Architecture Musings 
Hi
I will add a comment to my blog about promoting elements from SQL Adapter schemas to let readers know there is another option – the ony you have just described…
—
eliasen
Good one Richard –
Generally I’d say that manipulating generated schemas introduces a maintenance overhead, but as you would have to do this to set up the distinguished field anyway, using the built-in dialog or editing the schema by hand is just a tool selection, so there’s really no harm
Very useful to know.
Hey Yossi,
I agree about messing around with generated schemas. Needs to be well documented for future developers. However, one thing I liked was that this manual change was respected by the Editor tool. Unlike say, changing the underlying *.btm file in a map, and getting changes overwritten if you ever open the *.btm file in the mapper again.
Thanks very much for this. It was very helpful
Thanks Richard! Saved me some work today! 🙂
Happy to help 😉
Still hoping that in a future version we will be able to easily promote a field from the 1st occurrance of a repeating node. 😦