title: Insertion into multiple tables in sql server via xml
body: <p>I want to do inserts into multiple tables, i.e. <code>Customer, Account, AccountTransactions</code></p>
  
  <p><strong>Edit</strong></p>
  
  <ul>
  <li><code>Entity - Customer</code> one to one</li>
  <li><code>Customer - Account</code> is mapped as one to one </li>
  <li><code>Account - AccountTransactions</code> are mapped as one to many</li>
  </ul>
  
  <p><code>Entity(EntityId, EntityType)</code> EntityId primary key Auto incremented</p>
  
  <p><code>Customer(CustomerId, FName, LName)</code>  CustomerId = EntityId primary key</p>
  
  <p><code>Account(AccountId, AccountNo, CustomerId)</code>  AccountId PK, CustomerId FK</p>
  
  <p><code>AccountTransactions(TransactionId, PaymentDate, CurrentBalance, AccountId)</code> TransactionId PK, AccountId FK</p>
  
  <p>My XML is:</p>
  
  <pre><code>&lt;CustomerList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" &gt;
         &lt;Customer&gt;
                &lt;CustomerId/&gt;
                &lt;CustomerName&gt;Abhishek&lt;/CustomerName&gt;
                &lt;AccountId/&gt;
                &lt;AccountNumber&gt;eba5d378-b&lt;/AccountNumber&gt;
                &lt;Transactions&gt;
                       &lt;Transaction&gt;
                  &lt;TransactionId/&gt;
                              &lt;PaymentDate&gt;2/2/2012&lt;/PaymentDate&gt;
                              &lt;Amount&gt;500&lt;/Amount&gt;
                       &lt;/Transaction&gt;
                       &lt;Transaction&gt;
                  &lt;TransactionId/&gt;
                              &lt;PaymentDate&gt;2/2/2012&lt;/PaymentDate&gt;
                              &lt;Amount&gt;500&lt;/Amount&gt;
                       &lt;/Transaction&gt;
                &lt;/Transactions&gt;
            &lt;/Customer&gt;
         &lt;Customer&gt;
                &lt;CustomerId/&gt;
                &lt;CustomerName&gt;Yash&lt;/CustomerName&gt;
                &lt;AccountId/&gt;
                &lt;AccountNumber&gt;A101202&lt;/AccountNumber&gt;
                &lt;Transactions&gt;
                       &lt;Transaction&gt;
                  &lt;TransactionId/&gt;
                              &lt;PaymentDate&gt;2/2/2012&lt;/PaymentDate&gt;
                              &lt;Amount&gt;500&lt;/Amount&gt;
                       &lt;/Transaction&gt;
                       &lt;Transaction&gt;
                  &lt;TransactionId/&gt;
                              &lt;PaymentDate&gt;2/2/2012&lt;/PaymentDate&gt;
                              &lt;Amount&gt;500&lt;/Amount&gt;
                       &lt;/Transaction&gt;
                &lt;/Transactions&gt;
         &lt;/Customer&gt;
  &lt;/CustomerList&gt;
  </code></pre>
  
  <p>I want to insert into <code>Customer, Account, Transaction</code> table for each customer in xml and upon insertion into customer its id should be saved back into xml and also used in <code>Account</code> table as a foreign key</p>
  
  <p>I can see only way is to use nested cursor or nested while loop. Do any better method exists?</p>