Execute the following script in Query Editor to demonstrate the importing of XML bank transactions data into a table.

USE tempdb
Go
CREATE TABLE TransactionStage (
AccountNo char(17),
DepositOrWithdrawal char(1),
Amount MONEY
)
GO

CREATE PROC procUploadTransactions @TranSet XML
AS
SET NOCOUNT ON
DECLARE @pointer INT

EXEC sp_XML_preparedocument @pointer output, @TranSet

INSERT TransactionStage
SELECT *
FROM OPENXML(@pointer, 'data/tran')
WITH TransactionStage

EXEC sp_XML_removedocument @pointer
GO

declare @BankTran XML

set @BankTran=' <data>
<tran AccountNo="12345678901234567" DepositOrWithdrawal="D" Amount="300" />
<tran AccountNo="22345678901234567" DepositOrWithdrawal="D" Amount="3300" />
<tran AccountNo="32345678901234567" DepositOrWithdrawal="D" Amount="1300" />
<tran AccountNo="42345678901234567" DepositOrWithdrawal="W" Amount="500" />
<tran AccountNo="52345678901234567" DepositOrWithdrawal="D" Amount="1300.56" />

</data>'
exec procUploadTransactions @BankTran
GO

SELECT * FROM TransactionStage
GO