SQL通过xml插入批量数据

存储过程：

CREATE PROCEDURE [dbo].[UP_PurchasexxxCard]
@OrderInfo XML
AS
BEGIN

SET NOCOUNT ON;
DECLARE @DateNow Datetime;

DECLARE @OrderNumber BIGINT;--订单编号
DECLARE @CustomerID INT;--用户编号
DECLARE @Amount DECIMAL(18,2);--订单金额
DECLARE @PaymentMethodCode VARCHAR(4);--支付方式
DECLARE @Memo nvarchar(max);--用户备注
DECLARE @CardType nvarchar(50);--用户证件类型
DECLARE @CardNo nvarchar(50);--用户证件编号
DECLARE @ShippingAreaId varchar(10);--收货地址编号
DECLARE @ShippingContactWith nvarchar(60);--收货人姓名
DECLARE @ShippingAddress nvarchar(400);--收货人地址
DECLARE @CellPhone nvarchar(20);--收货人手机号
DECLARE @Phone nvarchar(20);--电话
DECLARE @Fax nvarchar(20);--传真
DECLARE @Zip nvarchar(20);--邮编
DECLARE @StoreId varchar(20);--实体店编号
DECLARE @DeliverDate Datetime;--取货时间
DECLARE @NeedInvoice int;
DECLARE @InvoiceType nvarchar(50);--发票类型
DECLARE @InvoiceTitle nvarchar(200);--发票抬头
DECLARE @TakeCardDate Datetime;--实际取货时间
DECLARE @InUserId int;
DECLARE @InDate Datetime;
DECLARE @EditUserId int;
DECLARE @EditDate Datetime;

SELECT
@OrderNumber = @OrderInfo.value('(/xxxCardOrderInfo//OrderNumber/node())[1]', 'BIGINT'),
@CustomerID = @OrderInfo.value('(/xxxCardOrderInfo//CustomerId/node())[1]', 'INT'),
@Amount = @OrderInfo.value('(/xxxCardOrderInfo//Amount/node())[1]', 'DECIMAL(18,2)'),
@PaymentMethodCode = @OrderInfo.value('(/xxxCardOrderInfo//PaymentMethodCode/node())[1]', 'VARCHAR(4)'),
@Memo = @OrderInfo.value('(/xxxCardOrderInfo//Memo/node())[1]', 'NVARCHAR(MAX)'),
@CardType = @OrderInfo.value('(/xxxCardOrderInfo//CardType/node())[1]', 'nvarchar(50)'),
@CardNo = @OrderInfo.value('(/xxxCardOrderInfo//CardNo/node())[1]', 'nvarchar(50)'),
@ShippingAreaId = @OrderInfo.value('(/xxxCardOrderInfo//ShippingAreaId/node())[1]', 'varchar(10)'),
@ShippingContactWith = @OrderInfo.value('(/xxxCardOrderInfo//ShippingContactWith/node())[1]', 'nvarchar(60)'),
@ShippingAddress = @OrderInfo.value('(/xxxCardOrderInfo//ShippingAddress/node())[1]', 'nvarchar(400)'),
@CellPhone = @OrderInfo.value('(/xxxCardOrderInfo//CellPhone/node())[1]', 'nvarchar(20)'),
@Phone = @OrderInfo.value('(/xxxCardOrderInfo//Phone/node())[1]', 'nvarchar(20)'),
@Fax = @OrderInfo.value('(/xxxCardOrderInfo//Fax/node())[1]', 'nvarchar(20)'),
@Zip = @OrderInfo.value('(/xxxCardOrderInfo//Zip/node())[1]', 'nvarchar(20)'),
@StoreId = @OrderInfo.value('(/xxxCardOrderInfo//StoreId/node())[1]', 'varchar(20)'),
@DeliverDate = @OrderInfo.value('(/xxxCardOrderInfo//DeliverDate/node())[1]', 'Datetime'),
@NeedInvoice = @OrderInfo.value('(/xxxCardOrderInfo//NeedInvoice/node())[1]', 'nvarchar(50)'),
@InvoiceType = @OrderInfo.value('(/xxxCardOrderInfo//InvoiceType/node())[1]', 'nvarchar(50)'),
@InvoiceTitle = @OrderInfo.value('(/xxxCardOrderInfo//InvoiceTitle/node())[1]', 'nvarchar(200)')
SET @DateNow=GETDATE();

IF OBJECT_ID('#TempxxxCardOrderDetail') IS NOT NULL
DROP TABLE #TempxxxCardOrderDetail;



BEGIN TRY
BEGIN TRAN;

INSERT INTO [xxxEvent].[dbo].[xxxCardOrder]
([OrderNumber]
,[CustomerId]
,[OrderDate]
,[Amount]
,[Status]
,[PaymentMethodCode]
,[Memo]
,[CardType]
,[CardNo]
,[ShippingAreaId]
,[ShippingContactWith]
,[ShippingAddress]
,[CellPhone]
,[Phone]
,[Fax]
,[Zip]
,[StoreId]
,[DeliverDate]
,[NeedInvoice]
,[InvoiceType]
,[InvoiceTitle]
,[TakeCardDate]
,[InUserId]
,[InDate]
,[EditUserId]
,[EditDate])
VALUES
(@OrderNumber
,@CustomerID
,@DateNow
,@Amount
,0
,@PaymentMethodCode
,@Memo
,@CardType
,@CardNo
,@ShippingAreaId
,@ShippingContactWith
,@ShippingAddress
,@CellPhone
,@Phone
,@Fax
,@Zip
,@StoreId
,@DeliverDate
,@NeedInvoice
,@InvoiceType
,@InvoiceTitle
,null
,@CustomerID
,@DateNow
,@CustomerID
,@DateNow
);

SELECT
@OrderNumber AS OrderNumber,
T.c.value('xxxCardType[1]','decimal(10,1)') AS xxxCardType,
T.c.value('UnitPrice[1]','decimal(10,1)') AS UnitPrice,
T.c.value('Quantity[1]','int') AS Quantity
INTO #TempxxxCardOrderDetail FROM @OrderInfo.nodes('xxxCardOrderInfo/xxxCardOrderDetails/xxxCardOrderDetailInfo') T(c);

INSERT INTO xxxCardOrderDetail SELECT * FROM #TempxxxCardOrderDetail;
COMMIT TRAN;
IF OBJECT_ID('#TempxxxCardOrderDetail') IS NOT NULL
DROP TABLE #TempxxxCardOrderDetail;
END TRY

BEGIN CATCH
ROLLBACK TRAN;
IF OBJECT_ID('#TempxxxCardOrderDetail') IS NOT NULL
DROP TABLE #TempxxxCardOrderDetail;

END CATCH

END

 

 

执行存储过程：

exec dbo.UP_PurchasexxxCard '<xxxCardOrderInfo>
<CommonInfor />
<EditInfor>
<InUserID>0</InUserID>
<InDate>2013-07-01T13:57:41.9436718+08:00</InDate>
<EditUserID>0</EditUserID>
<EditDate>2013-07-01T13:57:41.9436718+08:00</EditDate>
</EditInfor>
<OrderNumber>1030701628317</OrderNumber>
<CustomerId>1715580</CustomerId>
<OrderDate>2013-07-01T13:57:41.9576732+08:00</OrderDate>
<Amount>8000</Amount>
<Status>0</Status>
<PaymentMethodCode>3</PaymentMethodCode>
<Memo />
<CardType>身份证</CardType>
<CardNo>361234562323562356</CardNo>
<ShippingAreaId>100020102</ShippingAreaId>
<ShippingContactWith>张三</ShippingContactWith>
<ShippingAddress>天津市市辖区大港区撒旦法</ShippingAddress>
<CellPhone>15625625623</CellPhone>
<Phone />
<Zip>100000</Zip>
<StoreId>1001</StoreId>
<DeliverDate>2013-07-10T00:00:00</DeliverDate>
<NeedInvoice>公司</NeedInvoice>
<InvoiceType />
<InvoiceTitle>SXT</InvoiceTitle>
<xxxCardOrderDetails>
<xxxCardOrderDetailInfo>
<OrderNumber>1030701628317</OrderNumber>
<xxxCardType>500.0</xxxCardType>
<UnitPrice>500.0</UnitPrice>
<Quantity>4</Quantity>
</xxxCardOrderDetailInfo>
<xxxCardOrderDetailInfo>
<OrderNumber>1030701628317</OrderNumber>
<xxxCardType>1000.0</xxxCardType>
<UnitPrice>1000.0</UnitPrice>
<Quantity>2</Quantity>
</xxxCardOrderDetailInfo>
<xxxCardOrderDetailInfo>
<OrderNumber>1030701628317</OrderNumber>
<xxxCardType>2000.0</xxxCardType>
<UnitPrice>2000.0</UnitPrice>
<Quantity>2</Quantity>
</xxxCardOrderDetailInfo>
</xxxCardOrderDetails>
</xxxCardOrderInfo>'