none
I can create promoted properties, but not distinguished fields, why? RRS feed

  • Question

  • Hi!

    I have just generated a schema from a stored procedure in SQL Server via the SQL adapter (note: not WCF-SQL) according to this guide from Microsoft: https://msdn.microsoft.com/en-us/library/aa562098(v=bts.20).aspx.

    My schema looks as follows (omitted the rest of the sequence elements for simplicity, they all look the same):

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://SchemaTargetNamespace" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    	<xs:annotation>
    		<xs:appinfo>
    			<msbtssql:sqlScript value="exec [sp_Name] @param=&quot; &quot;" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" />
    		</xs:appinfo>
    	</xs:annotation>
    	<xs:element name="Anstallda">
    		<xs:complexType>
    			<xs:sequence minOccurs="1" maxOccurs="1">
    				<xs:element name="UserId" xmlns:q1="http://SchemaTargetNamespace"  minOccurs="1" maxOccurs="1" type="q1:UserIdType" />
    			</xs:sequence>
    		</xs:complexType>
    	</xs:element>
    	<xs:complexType name="AnstalldType" />
    	<xs:complexType name="UserIdType">
    		<xs:simpleContent>
    			<xs:extension base="xs:string" />
    		</xs:simpleContent>
    	</xs:complexType>
    </xs:schema>

    When I click "show promotions" the add button for distinguished fields is greyed out, but I can promote the properties. Since I just want to get to the fields in the orchestration, I would like to make them distinguished, not promoted. Any ideas why this is not working?


    Wednesday, August 31, 2016 9:29 AM

Answers

  • Hi Josefine,

    Its a record, “record” is really just an XSD element, and that often auto-generated schemas build all the nodes as records. Screenshot below.

    There is way around for this. Refer: https://seroter.wordpress.com/2007/10/09/how-to-distinguish-biztalk-schema-record-nodes/

    You have to open XSD schema in the VS.NET XML Editor instead of the BizTalk Editor. then manually added a new “distinguished field” to the “properties” collection of the schema. After saving, open the schema once more in the BizTalk Editor,and now it will show up as a distinguished field in the “Promoted Properties” window.

    See below highlighted:


    Rachit Sikroria (Microsoft Azure MVP)



    Wednesday, August 31, 2016 11:23 AM
    Moderator

All replies

  • Hi

    Make sure that you first select an element in the schema that is guaranteed to occur at most a single time in the schema, before you click on "Show promotions". For example, the selected element cannot be a record(that has children), or it cannot be any element whose Max Occurs is more than 1.

    Please refer-

    How is it disabled? Is it the tab "Distinguished Fields" that is inaccessible, or is it the "Add >>" button that is disabled? The "Add >>" button will only be enabled if you are positioned on a single-occurrent Element or Attribute (actually it will also be enabled on a multi-occurent, but that will produce a warning if actually added)

    https://social.msdn.microsoft.com/Forums/en-US/44b0da15-6ac2-40c5-baf9-0c9d08eda91f/why-can-i-promote-property-fields-in-biztalk-2006-but-distinguished-fields-are-disabled?forum=biztalkgeneral


    Thanks Arindam



    Wednesday, August 31, 2016 9:36 AM
    Moderator
  • Hi Josefine,

    Anstallda annd userId both the fields are Record not a Field Element. Only Elements and Attributes can be distinguished.



    Rachit Sikroria (Microsoft Azure MVP)


    Wednesday, August 31, 2016 9:43 AM
    Moderator
  • Hi

    Make sure that you first select an element in the schema that is guaranteed to occur at most a single time in the schema, before you click on "Show promotions". For example, the selected element cannot be a record(that has children), or it cannot be any element whose Max Occurs is more than 1.

    Please refer-

    How is it disabled? Is it the tab "Distinguished Fields" that is inaccessible, or is it the "Add >>" button that is disabled? The "Add >>" button will only be enabled if you are positioned on a single-occurrent Element or Attribute (actually it will also be enabled on a multi-occurent, but that will produce a warning if actually added)

    https://social.msdn.microsoft.com/Forums/en-US/44b0da15-6ac2-40c5-baf9-0c9d08eda91f/why-can-i-promote-property-fields-in-biztalk-2006-but-distinguished-fields-are-disabled?forum=biztalkgeneral


    Thanks Arindam



    The add button is greyed out, and yes it is placed on a node that only can occur once, i.e. max = 1, min = 1.
    Wednesday, August 31, 2016 11:08 AM
  • Hi Josefine,

    Anstallda annd userId both the fields are Record not a Field Element. Only Elements and Attributes can be distinguished.



    Rachit Sikroria (Microsoft Azure MVP)


    What do you mean? I am trying to distinguish UserId and it is an element, as you can see in the schema.
    Wednesday, August 31, 2016 11:10 AM
  • Why are you using the SQL Adapter?
    Wednesday, August 31, 2016 11:21 AM
  • Hi Josefine,

    Its a record, “record” is really just an XSD element, and that often auto-generated schemas build all the nodes as records. Screenshot below.

    There is way around for this. Refer: https://seroter.wordpress.com/2007/10/09/how-to-distinguish-biztalk-schema-record-nodes/

    You have to open XSD schema in the VS.NET XML Editor instead of the BizTalk Editor. then manually added a new “distinguished field” to the “properties” collection of the schema. After saving, open the schema once more in the BizTalk Editor,and now it will show up as a distinguished field in the “Promoted Properties” window.

    See below highlighted:


    Rachit Sikroria (Microsoft Azure MVP)



    Wednesday, August 31, 2016 11:23 AM
    Moderator
  • Why are you using the SQL Adapter?

    I do not have control over the adapters that are being used at this client, they are still using the old one and going by the device "don't fix what is not broken".
    Wednesday, August 31, 2016 11:32 AM
  • I do not have control over the adapters that are being used at this client, they are still using the old one and going by the device "don't fix what is not broken". 

    If they are using BizTalk Server 2006 R2 or later, the legacy SQL Adapter has been deprecated so I would argue that it's already broken.  This is a problem.

    We're happy to help you explain why this is a horrible decision.

    Wednesday, August 31, 2016 11:55 AM
  • Hi Josefine,

    Its a record, “record” is really just an XSD element, and that often auto-generated schemas build all the nodes as records. Screenshot below.

    There is way around for this. Refer: https://seroter.wordpress.com/2007/10/09/how-to-distinguish-biztalk-schema-record-nodes/

    You have to open XSD schema in the VS.NET XML Editor instead of the BizTalk Editor. then manually added a new “distinguished field” to the “properties” collection of the schema. After saving, open the schema once more in the BizTalk Editor,and now it will show up as a distinguished field in the “Promoted Properties” window.
    ....


    Rachit Sikroria (Microsoft Azure MVP)


    Sorry - now I see you are right. I was just looking at the code itself and not the properties window.

    I think I will go with the promoted properties instead, it feels more safe than to modify the generated schema.

    Big thanks!

    Wednesday, August 31, 2016 12:03 PM
  • I do not have control over the adapters that are being used at this client, they are still using the old one and going by the device "don't fix what is not broken". 

    If they are using BizTalk Server 2006 R2 or later, the legacy SQL Adapter has been deprecated so I would argue that it's already broken.  This is a problem.

    We're happy to help you explain why this is a horrible decision.

    I know it is a bad decision to use the deprecated adapter, and I will of course talk to them about it, but as for this integration I cannot change it - I need to deliver a working solution first. 
    Wednesday, August 31, 2016 12:06 PM
  • Sorry to point out, but a working solution must also be a correct solution as much as possible.  But using unsupported features, you are introducing problems that will have to be fixed later.

    The change is very minor, right now.  To correct this later will require a lot more refactoring.

    Let us help you address the real problem.

    Wednesday, August 31, 2016 12:34 PM
  • Sorry to point out, but a working solution must also be a correct solution as much as possible.  But using unsupported features, you are introducing problems that will have to be fixed later.

    The change is very minor, right now.  To correct this later will require a lot more refactoring.

    Let us help you address the real problem.

    No, the change is not minor, since they have several integrations running with this adapter now, and does not have the WCF-SQL adapter installed. The refactoring added by this integration is minor, in relation to what already needs to be changed.

    Wednesday, August 31, 2016 12:57 PM
  • In that case, the marked Answer is not correct.  There is a reason Record Elements cannot be Promoted or Distinguished, even Mixed content.

    But, let's step back one.  Since you have to the the legacy SQL Adapter, the preferred way to create Schemas is by using SQL Server itself.  You do this by temporarily adding WITH XMLNAMESPACES to the final SELECT.  Since there is no inference, that should product the correct Element representations, namely Field.

    However, if that is not possible, the way to solve this is to correct the Schema definition so the Elements appear as Fields not Records.  The only different between Fields and Records is the defined Type, xsd primitive or complex.

    If you're trying to Distinguish UserID, all you have to do is change it's type from the references ComplexType to xs:string.  Like this:

    <xs:element name="UserId" xmlns:q1="http://SchemaTargetNamespace"  minOccurs="1" maxOccurs="1" type="xs:string" />

    You can then delete the UserIDType complexType and the namespace reference.  You can leave them, they'll just have no effect.

    Wednesday, August 31, 2016 1:44 PM
  • Hi John,

    I beg to differ, I don't find anything wrong in Richard Seroter's approach in his article. If it works, as proved it does and there are no negative implications. Why not?


    Rachit Sikroria (Microsoft Azure MVP)

    Wednesday, August 31, 2016 2:28 PM
    Moderator
  • Because the problem is not the Promoted Properties dialog box, which is behaving correctly.

    The problem the is Schema Wizard incorrectly guessed the Element composition as Record.

    Correcting the Element's definition solves the actual problem so the Schema Editor can function as designed.

    Wednesday, August 31, 2016 3:07 PM
  • In that case, the marked Answer is not correct.  There is a reason Record Elements cannot be Promoted or Distinguished, even Mixed content.

    But, let's step back one.  Since you have to the the legacy SQL Adapter, the preferred way to create Schemas is by using SQL Server itself.  You do this by temporarily adding WITH XMLNAMESPACES to the final SELECT.  Since there is no inference, that should product the correct Element representations, namely Field.

    However, if that is not possible, the way to solve this is to correct the Schema definition so the Elements appear as Fields not Records.  The only different between Fields and Records is the defined Type, xsd primitive or complex.

    If you're trying to Distinguish UserID, all you have to do is change it's type from the references ComplexType to xs:string.  Like this:

    <xs:element name="UserId" xmlns:q1="http://SchemaTargetNamespace"  minOccurs="1" maxOccurs="1" type="xs:string" />

    You can then delete the UserIDType complexType and the namespace reference.  You can leave them, they'll just have no effect.

    Sorry for late answer, but thanks for your input! I will keep the marked answer as the correct one, since it is a solution to the problem at hand. I think it is a valid problem situation, since I have followed Microsofts own guide on using the SQL adapter to generate a schema. But you make a very good point on the issues and hopefully will this help someone else with a similar problem that could be fixed in another way. :)


    Friday, September 9, 2016 2:05 PM