通过xml插入sql server中的多个表 [英] Insertion into multiple tables in sql server via xml

问题描述

我想插入多个表，即Customer、Account、AccountTransactions

编辑

• 实体 - 客户一对一
• Customer - Account 一对一映射
• Account - AccountTransactions 映射为一对多

• Entity - Customer one to one
• Customer - Account is mapped as one to one
• Account - AccountTransactions are mapped as one to many

Entity(EntityId, EntityType) EntityId 主键自增

Entity(EntityId, EntityType) EntityId primary key Auto incremented

Customer(CustomerId, FName, LName) CustomerId = EntityId 主键

Customer(CustomerId, FName, LName) CustomerId = EntityId primary key

Account(AccountId, AccountNo, CustomerId) AccountId PK, CustomerId FK

Account(AccountId, AccountNo, CustomerId) AccountId PK, CustomerId FK

AccountTransactions(TransactionId, PaymentDate, CurrentBalance, AccountId) TransactionId PK, AccountId FK

AccountTransactions(TransactionId, PaymentDate, CurrentBalance, AccountId) TransactionId PK, AccountId FK

我的 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>

我想在 xml 中为每个客户插入 Customer, Account, Transaction 表，并在插入客户后，其 ID 应保存回 xml 并在 Account 表作为外键

I want to insert into Customer, Account, Transaction table for each customer in xml and upon insertion into customer its id should be saved back into xml and also used in Account table as a foreign key

我能看到的唯一方法是使用嵌套游标或嵌套 while 循环.有没有更好的方法?

I can see only way is to use nested cursor or nested while loop. Do any better method exists?

推荐答案

假设您有适当的表 - 您绝对可以执行迭代方法而没有任何杂乱无章的光标！

Assuming you have the proper tables in place - you can definitely do an iterative approach without any messy kludgy cursor!

尝试这样的事情 - 这将暂时处理客户和帐户，但您绝对可以将其扩展到交易.

Try something like this - this will handle customers and accounts for now, but you can definitely extend this to the transactions, too.

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

现在在下一步中，您可以解析每个客户/帐户对的交易并将它们插入到适当的表中.

Now in a next step, you could parse the transaction for each customer/account pair and insert those into the appropriate table.

这篇关于通过xml插入sql server中的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！