none
Schema creation from procedure RRS feed

  • Question

  • Hi ,

    i have a procedure having 3 xml auto  tags(returning data from 3 selects),

    while creating schema i just got first xml auto, i didnt get the other two.

    how can i achieve this.

    Thursday, July 14, 2016 7:04 AM

Answers

  • So, here the thing, it really doesn't work like that even though you can the schema for this with all 4 result sets.

    Your bigger problem is you can't mix Result Set and Xml content in the same response.   Well, you can, but the Xml is then treated like a string.

    Do you really need to return the last two as Xml?  It would be easier to just treat them as tables.

    Thursday, July 14, 2016 12:34 PM
    Moderator

All replies

  • Hi,

    Which Adapter are you using to SQL Adapter or WCF-SQL ?

    just note that SQL Adapter is not supported to returning multiple result set, so in your can it will always return first result set only.

    https://msdn.microsoft.com/en-us/library/ms935658(v=bts.10).aspx

    you can modify to return multiple records in single result set instead.

    Hope this Helps!!!

    Please Mark as Answered if you satisfy with Reply.

    Thursday, July 14, 2016 7:34 AM
  • Hi DineshNad,

    Please share a little bit more information about how you performed the steps and the resultant schema generated. It is hard to suggest any thing based upon the amount of information shared.

    Regards,


    Mandar Dharmadhikari

    Thursday, July 14, 2016 7:37 AM
    Moderator
  • create proc test

     @a int,

    @b int,c int

    as

    begin

     select * into Temp1  from table1

     select * into Temp2  from table2

    select * from Temp1   for XML AUTO,XMLDATA

    select * from Temp2   for XML AUTO,XMLDATA

    end


    • Edited by DineshNad Thursday, July 14, 2016 7:50 AM
    Thursday, July 14, 2016 7:49 AM
  • Hi Dinesh,

    while creating schema i just got first xml auto, i didnt get the other two.

    How are you creating the schema?

    Add Generated  items I suppose....

    Also your request schema will contain only the parameters you have specified in the parameters...so ideally your request should have a, b, c as three nodes inside the root node....also have you checked the stored procedure by executing it manually on the db?

    also do share the schema generated by the wizard

    Regards,


    Mandar Dharmadhikari



    Thursday, July 14, 2016 7:53 AM
    Moderator
  • So, here the thing, it really doesn't work like that even though you can the schema for this with all 4 result sets.

    Your bigger problem is you can't mix Result Set and Xml content in the same response.   Well, you can, but the Xml is then treated like a string.

    Do you really need to return the last two as Xml?  It would be easier to just treat them as tables.

    Thursday, July 14, 2016 12:34 PM
    Moderator
  • Hi johns,

    i am selecting form table1,table2 and storing in to temp tables Temp1,Temp2

    and i am selecting from these temptables as xml using XML AUTO.

    but while creating schema in biztalk using add generated items process i am just getting only Temp1  details but not Temp2 how can i get Both at a time.

    create proc test

     @a int,

    @b int,c int

    as

    begin

     select * into Temp1  from table1

     select * into Temp2  from table2

    select * from Temp1   for XML AUTO,XMLDATA

    select * from Temp2   for XML AUTO,XMLDATA

    end

    because i have a procedure contains more than 10 XML AUTO  for returning data from multiple selects. I am getting first XML AUTO related schema but not the remaining.

    I can solve this issue by commenting  one XML AUTO each time in order , but i need to do the process for 10+ times.

    is there any solution for this .

    Please help me. 


    • Edited by DineshNad Saturday, July 16, 2016 10:09 AM
    Saturday, July 16, 2016 9:30 AM
  • You can do something like:

    declare @foo xml
    declare @bar xml
    
    set @foo = (select * from MyFooTable for xml auto)
    set @bar = (select * from MyBarTable for xml auto)
    
    select * from (select @foo as foo, @bar as bar) as combo for xml auto


    Saturday, July 16, 2016 8:44 PM
    Moderator