Re: WCF-SQL Adapter Bulk Inserts with Foreign Keys?

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › WCF-SQL Adapter Bulk Inserts with Foreign Keys? › Re: WCF-SQL Adapter Bulk Inserts with Foreign Keys?

September 14, 2009 at 2:54 PM #23243
community-content

    Greg, I wanted to let you know this is working for me great so far for the most part.  I still have to test a very large file and performance benchmark it, but I suspect it won’t have the 10,000 variable limit issue that the Updategram has since it’s being passed all as XML.

    I did run into one issue – when the orchestration passes the sql parm it’s including the namespace.  I was able to get my stored procedure to work by manually executing it without the namespace.  I am not sure if my constructed message is incorrect in the orchestration or my XQuery should work around the namespace?

    So my XML parameter passed =
    ‘<nso:MyOrder xmlns:nso=http://my namespace.load_schema”>
        <Orders>
            <Order>
                <PONum>123</PONum>
            </Order>
        <Orders>
    </MyOrder>

    And my sproc looks something like this…
    INSERT INTO dbo.Orders
     (
          PONumber   
     )   
      SELECT
       Table1.Column1.value(‘PONum[1]’, ‘CHAR(1)’)
      FROM
       @LoadXMLData.nodes(‘/Orders/Order’) AS Table1(Column1) 

    When i manually remove the namespace, it works with just this XML…
        <Orders>
            <Order>
                <PONum>123</PONum>
            </Order>
        <Orders>