none
SQL Schema Transform Issue in BTS 2006 RRS feed

  • Question

  • Dear All,

    I have generated schema of SQL stored procedure in orchestration and then i like to transform to another schema, but while doing this the output file after transform is empty.

    Please advise the issue.

    I Have generated instance of SQL schema and it shows like

    
    <ns0:RequestSPDAN xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003">
      <ns0:DAN_TransationDetail_DC SerialNo="SerialNo_0" FERT="FERT_1" DealerCode="DealerCode_2" DealerName="DealerName_3" CustomerName="CustomerName_4" CustomerMob="CustomerMob_5" DCSEnquiryNo="DCSEnquiryNo_6" GroupDAN="10" DiscountAmt="DiscountAmt_8" DealerShare="DealerShare_9" SalesQuantity="SalesQuantity_10" ClosedDate="ClosedDate_11" Validity="Validity_12" CreatedOn="CreatedOn_13" ApprovedBy="ApprovedBy_14" Offer1="Offer1_15" OfferAmt1="OfferAmt1_16" Offer2="Offer2_17" Offer2Amt="Offer2Amt_18" Offer3="Offer3_19" Offer3Amt="Offer3Amt_20" Offer4="Offer4_21" Offer4Amt="Offer4Amt_22" Active="10" />

     <ns0:DAN_TransationDetail_DCSerialNo="SerialNo_0" FERT="FERT_1" DealerCode="DealerCode_2" DealerName="DealerName_3" CustomerName="CustomerName_4" CustomerMob="CustomerMob_5" DCSEnquiryNo="DCSEnquiryNo_6" GroupDAN="10" DiscountAmt="DiscountAmt_8" DealerShare="DealerShare_9" SalesQuantity="SalesQuantity_10" ClosedDate="ClosedDate_11" Validity="Validity_12" CreatedOn="CreatedOn_13" ApprovedBy="ApprovedBy_14" Offer1="Offer1_15" OfferAmt1="OfferAmt1_16" Offer2="Offer2_17" Offer2Amt="Offer2Amt_18" Offer3="Offer3_19" Offer3Amt="Offer3Amt_20" Offer4="Offer4_21" Offer4Amt="Offer4Amt_22" Active="10" />

    </ns0:RequestSPDAN>

    


    -- Regards Sandeep

    Saturday, June 18, 2016 6:54 AM

All replies

  • Hi Sandeep

    You have to check your map logic. Make sure that the source schema in the map matches the input XML you are providing(including namespaces).

    Do a Test Map in Visual Studio. First try with 'TestMap Input' as Generate Instance from the Map properties page. See if it makes a difference.

    Also try with 'TestMap Input' as XML and provide the path to your SQL schema instance from above.

    If both fail to produce output, right click the map and do 'Debug Map'. You can step through the XSLT code and check why no output gets generated.


    Thanks Arindam



    Saturday, June 18, 2016 7:19 AM
    Moderator
  • Just noticed that your input XML is invalid. 

    In the 2nd element - <ns0:DAN_TransationDetail_DCSerialNo="SerialNo_0" has no space separating the attribute. It should be - <ns0:DAN_TransationDetail_DC SerialNo="SerialNo_0" ..

    So, valid XML is as follows- (you can copy this)

    <ns0:RequestSPDAN xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003">
      <ns0:DAN_TransationDetail_DC SerialNo="SerialNo_0" FERT="FERT_1" DealerCode="DealerCode_2" DealerName="DealerName_3" CustomerName="CustomerName_4" CustomerMob="CustomerMob_5" DCSEnquiryNo="DCSEnquiryNo_6" GroupDAN="10" DiscountAmt="DiscountAmt_8" DealerShare="DealerShare_9" SalesQuantity="SalesQuantity_10" ClosedDate="ClosedDate_11" Validity="Validity_12" CreatedOn="CreatedOn_13" ApprovedBy="ApprovedBy_14" Offer1="Offer1_15" OfferAmt1="OfferAmt1_16" Offer2="Offer2_17" Offer2Amt="Offer2Amt_18" Offer3="Offer3_19" Offer3Amt="Offer3Amt_20" Offer4="Offer4_21" Offer4Amt="Offer4Amt_22" Active="10" />
     <ns0:DAN_TransationDetail_DC SerialNo="SerialNo_0" FERT="FERT_1" DealerCode="DealerCode_2" DealerName="DealerName_3" CustomerName="CustomerName_4" CustomerMob="CustomerMob_5" DCSEnquiryNo="DCSEnquiryNo_6" GroupDAN="10" DiscountAmt="DiscountAmt_8" DealerShare="DealerShare_9" SalesQuantity="SalesQuantity_10" ClosedDate="ClosedDate_11" Validity="Validity_12" CreatedOn="CreatedOn_13" ApprovedBy="ApprovedBy_14" Offer1="Offer1_15" OfferAmt1="OfferAmt1_16" Offer2="Offer2_17" Offer2Amt="Offer2Amt_18" Offer3="Offer3_19" Offer3Amt="Offer3Amt_20" Offer4="Offer4_21" Offer4Amt="Offer4Amt_22" Active="10" /></ns0:RequestSPDAN>


    Thanks Arindam



    Saturday, June 18, 2016 7:31 AM
    Moderator
  • Hi Arindam,

    Before reading your reply..i tried at SQL end and in stored procedure in changed line "for xml auto,elements" to

    "for xml auto" and it worked.

    As i assume when you right click on SQL schema and click generate instance it shows schema as shown above.

    But when i execute SP with line "for xml auto,elements" i get xml as 

    DAN_TransationDetail_DC>
      <SerialNo>XYZ-D2-130616-00005</SerialNo>
      <FERT>80501027</FERT>
      <DealerCode>1xxx001</DealerCode>
      <DealerName>test dealer</DealerName>
      <CustomerName>RetailDANCustomer1</CustomerName>
      <CustomerMob>9999999999</CustomerMob>
      <DCSEnquiryNo>123456789</DCSEnquiryNo>
      <GroupDAN>0</GroupDAN>
      <DiscountAmt>10</DiscountAmt>
      <DealerShare>10</DealerShare>
      <SalesQuantity>1</SalesQuantity>
      <ClosedDate>2016-06-17</ClosedDate>
      <Validity>2016-06-17</Validity>
      <CreatedOn>2016-06-17</CreatedOn>
      <ApprovedBy>user</ApprovedBy>
      <Offer1>AMC</Offer1>
      <OfferAmt1>1000</OfferAmt1>
      <Offer2>test6 </Offer2>
      <Offer2Amt>1000</Offer2Amt>
      <Offer3>test3 </Offer3>
      <Offer3Amt>1000</Offer3Amt>
      <Offer4>test2 </Offer4>
      <Offer4Amt>1000</Offer4Amt>
      <Active>1</Active>
    </DAN_TransationDetail_DC>

    Which doesn't matches with generate instance xml ..but when i use "for xml auto only and execute SP it shows xml as 

    DAN_TransationDetail_DC SerialNo="xxx-x2-130616-00005" FERT="666666" DealerCode="1xx001" DealerName=testdealer" CustomerName="RetailDANCustomer1" CustomerMob="9999999999" DCSEnquiryNo="123456789" GroupDAN="0" DiscountAmt="10" DealerShare="10" SalesQuantity="1" ClosedDate="2016-06-17" Validity="2016-06-17" CreatedOn="2016-06-17" ApprovedBy="svhanda" Offer1="AMC" OfferAmt1="1000" Offer2="test 2 " Offer2Amt="1000" Offer3="test 3 " Offer3Amt="1000" Offer4="test3 " Offer4Amt="1000" Active="1" />

    which matches with schema..pls advise if my understanding is correct.


    -- Regards Sandeep

    Saturday, June 18, 2016 10:38 AM
  • Hi Sandeep

    It seems that when you generated the SQL schemas pointing at the stored proc, the SQL stored proc definition contained "for xml auto", and not "for xml auto,elements". Your generated schema conforms to "for xml auto".

    "for xml auto,elements" causes the SQL column fields to become child elements of the row entity as you are seeing-

    DAN_TransationDetail_DC>
      <SerialNo>XYZ-D2-130616-00005</SerialNo>
      <FERT>80501027</FERT>

    ...

    "for xml auto" causes the SQL column fields to become attributes of the parent entity, so you get-

    DAN_TransationDetail_DC SerialNo="xxx-x2-130616-00005" FERT="666666" DealerCode="1xx001" DealerName=testdealer" ...

    You would not need either "for xml auto,elements" or "for xml auto" statements to be specified in SQL Server stored proc if you are using the WCF-SQL adapter. These were needed with the old SQL adapter with BizTalk 2006 - which is now deprecated. In newer BizTalk versions, you can write your stored proc as any normal stored proc, when you run the Consume Adapter Service Wizard from Visual Studio, it will generate the Stored Proc schemas correctly for you. At runtime, the adapter generates the correct XML structure from the SQL resultset.

    Refer the recommended steps to work with SQL Server stored procedures and the WCF-SQL adapter below. The sample uses a SQL database hosted in Windows Azure, same steps apply for your local SQL Server-

    http://social.technet.microsoft.com/wiki/contents/articles/19799.biztalk-server-2013-wcf-sql-executing-a-stored-procedure-in-windows-azure-sql-database.aspx


    Thanks Arindam





    Saturday, June 18, 2016 11:32 AM
    Moderator
  • So, what exactly do you mean by 'empty'?  That actually implies several things.

    Does the message you're testing with Validate against the Schema?  That is the first thing you have to confirm.

    Sunday, June 19, 2016 12:09 PM
  • Dear Johns,

    Empty mean after I executed BizTalk project the file coming after transform doesn't have any tags.

    @Arundam: I am using BTS 2006 hence for SQL adapter I need to use for xml auto,elements.

    Only my query is when we in visual studio right click on schema and select Generate instance why it shows as SQL column fields as attributes


    -- Regards Sandeep

    Monday, June 20, 2016 5:34 AM
  • Hi Sandeep

    It seems that when you generated the SQL schemas pointing at the stored proc, the SQL stored proc definition contained "for xml auto", and not "for xml auto,elements". Your generated schema conforms to "for xml auto".

    It is not an issue with Generate instance. Try to generate the schema once again now that you have changed the Stored Proc to be "for xml auto". The schema will get created accordingly and Generate Instance will create matching message.


    Thanks Arindam

    Monday, June 20, 2016 5:48 AM
    Moderator
  • Does the source message Validate against the Schema?

    That will probably tell you exactly what the problem is.

    Monday, June 20, 2016 12:07 PM
  • Dear Arindam,

    I will check for the SQL schema generation. But usually I use "for xml auto,xml data" for schema generation and when schema is generated in BizTalk then I replace line in SQL SP with "for xml auto,elements"


    -- Regards Sandeep

    Tuesday, June 21, 2016 4:08 AM