插入通过XML在SQL Server的多个表(Insertion into multiple tab

我想要做的插入到多个表，即Customer, Account, AccountTransactions

编辑

Entity - Customer一对一
Customer - Account被映射为一对一
Account - AccountTransactions被映射为一对多

Entity(EntityId, EntityType) ENTITYID主键自动递增

Customer(CustomerId, FName, LName)客户ID = ENTITYID主键

Account(AccountId, AccountNo, CustomerId) ACCOUNTID PK，客户ID FK

AccountTransactions(TransactionId, PaymentDate, CurrentBalance, AccountId) TRANSACTIONID PK，FK ACCOUNTID

我的XML是：

<CustomerList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
       <Customer>
              <CustomerId/>
              <CustomerName>Abhishek</CustomerName>
              <AccountId/>
              <AccountNumber>eba5d378-b</AccountNumber>
              <Transactions>
                     <Transaction>
                <TransactionId/>
                            <PaymentDate>2/2/2012</PaymentDate>
                            <Amount>500</Amount>
                     </Transaction>
                     <Transaction>
                <TransactionId/>
                            <PaymentDate>2/2/2012</PaymentDate>
                            <Amount>500</Amount>
                     </Transaction>
              </Transactions>
          </Customer>
       <Customer>
              <CustomerId/>
              <CustomerName>Yash</CustomerName>
              <AccountId/>
              <AccountNumber>A101202</AccountNumber>
              <Transactions>
                     <Transaction>
                <TransactionId/>
                            <PaymentDate>2/2/2012</PaymentDate>
                            <Amount>500</Amount>
                     </Transaction>
                     <Transaction>
                <TransactionId/>
                            <PaymentDate>2/2/2012</PaymentDate>
                            <Amount>500</Amount>
                     </Transaction>
              </Transactions>
       </Customer>
</CustomerList>

我要插入到Customer, Account, Transaction表中的XML和在插入到客户每个客户其ID应该被保存回XML并在使用Account表的外键

我可以看到唯一的办法就是使用嵌套游标或嵌套while循环。是否有任何更好的方法存在？

Answer 1:

假设您已经准备好正确的表 - 你绝对可以做一个迭代的方法没有任何凌乱的缺憾光标！

尝试这样的事情 - 这将处理客户和帐户现在，但你绝对可以这样延伸到交易了。

declare @input XML = '... your XML here .....';

CREATE TABLE #CustAcct (CustomerName VARCHAR(50), CustomerID INT, AcctNumber VARCHAR(50), AcctID INT);

-- first extract customer and account into from the XML, using a common table expression    
WITH CustomersAndAccounts AS
(
   SELECT
       CustomerName = CL.Cust.value('(CustomerName)[1]', 'varchar(50)'),
       AcctNumber = CL.Cust.value('(AccountNumber)[1]', 'varchar(50)')
   FROM 
       @input.nodes('/CustomerList/Customer') CL(Cust)
)
INSERT INTO #CustAcct(CustomerName, AcctNumber)
    SELECT CustomerName, AcctNUmber
    FROM CustomersAndAccounts

-- insert customers into 'Customer' table    
INSERT INTO Customer(CustomerName)
    SELECT CustomerName
    FROM #CustAcct

-- update the temporary working table with the appropriate ID's from the 'Customer' table    
UPDATE #CustAcct
SET CustomerID = c.CustomerID
FROM Customer c
WHERE #CustAcct.CustomerName = c.CustomerName

-- insert values into 'Account' table from the working table   
INSERT INTO Account(CustomerID, AccountNumber)
    SELECT CustomerID, AcctNumber
    FROM #CustAcct

-- update the working table from the values inserted
UPDATE #CustAcct
SET AcctID = a.AccountID
FROM Account a
WHERE #CustAcct.CustomerID = a.CustomerID AND #CustAcct.AcctNumber = a.AccountNumber

SELECT * FROM #CustAcct

现在，在接下来的步骤，你可以为每个客户/帐户对解析事务，并将这些到相应的表中。