none
How to select from table1 and insert result in table3 RRS feed

  • Question

  • Hi,

    I have a scenario and i hope someone can help me,

    i need to do the following:

    1 - select all data from table3 (id integer, int3 integer ,str3 varchar(255))
    2 - insert the results into table1 (id integer, int1 integer ,str1 varchar(255))


    i tried the following:

    1 - created a receive port (XML FILE) :

    <ns0:table3doc xmlns:ns0="http://table3sel/">
      <ns0:seltable3 id="0"/>
    </ns0:table3doc>

    /////////////// seltable3 is a pre defined procedure (SELECT * FROM table3 for xml auto, xmldata)

    2 - linked this port with the request of a SQLPort (for selecting from table3), and linked the output to a sending port (XML FILE), and i got this result :


    <?xml version="1.0" encoding="utf-16"?>
    <table3doc xmlns="http://table3sel/">
    <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <ElementType name="table3" content="empty" model="closed">
    <AttributeType name="id" dt:type="i4" />
    <AttributeType name="int3" dt:type="i4" />
    <AttributeType name="str3" dt:type="string" />
    <attribute type="id" />
    <attribute type="int3" />
    <attribute type="str3" />
    </ElementType>
    </Schema>
    <table3 xmlns="x-schema:#Schema1" id="1" int3="1" str3="a         " />
    <table3 xmlns="x-schema:#Schema1" id="2" int3="2" str3="b         " />
    <table3 xmlns="x-schema:#Schema1" id="3" int3="3" str3="c         " />
    <table3 xmlns="x-schema:#Schema1" id="4" int3="4" str3="d         " />
    <table3 xmlns="x-schema:#Schema1" id="5" int3="5" str3="e         " />
    </table3doc>

    3 - the response of the SQLPort is also connected to a transformer, this transformer maps the feilds of the two tables (table3 -> table1)

    table3        table1
       int3    ->    int1
       str3    ->    str1

    4 - the result of this mapping goes into another sending XML File Port, and a sending port SQLPort2 which must insert the data into table1.



    my problem is, this thing is not working, i get the results of table3, but the map result is always empty (there is no body in the XML), maybe there is something i should do to the XML, here it is:


    <?xml version="1.0" encoding="utf-8"?>
    <ns0:table1req xmlns:ns0="http://table1ins/>
    </ns0:table1req>


    please anyone, just give me any other way, or help me with this, i need to select all fields from table3 and insert the results in table1.

    thank you.

    Sunday, March 1, 2009 8:34 PM

Answers

  • Hi

    Have you modified your stored procedure "seltable3" by stripping off the xmldata part. As in, we attach the word xmldata only at the time of schema generation. You need to remove the part xmldata at the time of actually running the stored proc. It should now only be "SELECT * FROM table3 for xml auto". This is a part of the documentation:
    http://msdn.microsoft.com/en-us/library/aa560708.aspx

    Note
    The SQL adapter only handles stored procedures that return XML. In this example, there is one simple SELECT FROM table FOR xml auto. Examine the stored procedure SP_Get_Agent_and_Interest() for a procedure that returns one or two items that you need for XML raw data. The added , xmldata would cause the SQLXML to emit a schema as well as data. The SQL Add Adapter Wizard uses , xmldata, which you must remove before actual operation of the SQL adapter or else the schema will confuse the BizTalk Messaging Engine.
    • Marked as answer by TamirMalas Monday, March 2, 2009 10:41 AM
    Monday, March 2, 2009 5:42 AM

All replies

  • Hi

    Have you modified your stored procedure "seltable3" by stripping off the xmldata part. As in, we attach the word xmldata only at the time of schema generation. You need to remove the part xmldata at the time of actually running the stored proc. It should now only be "SELECT * FROM table3 for xml auto". This is a part of the documentation:
    http://msdn.microsoft.com/en-us/library/aa560708.aspx

    Note
    The SQL adapter only handles stored procedures that return XML. In this example, there is one simple SELECT FROM table FOR xml auto. Examine the stored procedure SP_Get_Agent_and_Interest() for a procedure that returns one or two items that you need for XML raw data. The added , xmldata would cause the SQLXML to emit a schema as well as data. The SQL Add Adapter Wizard uses , xmldata, which you must remove before actual operation of the SQL adapter or else the schema will confuse the BizTalk Messaging Engine.
    • Marked as answer by TamirMalas Monday, March 2, 2009 10:41 AM
    Monday, March 2, 2009 5:42 AM
  • Hi,

    Thank you, after i removed the xmldata, everything worked successfuly.
    Monday, March 2, 2009 10:42 AM