TSQL : Insert XML To Database Table(s)

CREATE TABLE sales (
POSName varchar(255),
BillNo varchar(255),
Date date,
Time datetime,
SubTotal decimal(18,2),
DiscountAmount decimal(18,2),
GrandTotalAmount decimal(18,2),
SettelementMode varchar(255),
TransactionStatus varchar(255)
);

CREATE TABLE taxes (
BillNo varchar(255),
TaxDesciption varchar(255),
TaxAmount decimal(18,2)
);

DECLARE @xml xml = ‘NavaFP01000012022-12-1712:20:43 AM0.00.00.0S C 10 %0.0C.Gst 2.5 %0.0S.Gst 2.5 %0.0NcSale‘;

INSERT INTO sales (POSName, BillNo, Date, Time, SubTotal, DiscountAmount, GrandTotalAmount, SettelementMode, TransactionStatus)
SELECT r.value(‘POSName[1]’, ‘varchar(255)’),
r.value(‘BillNo[1]’, ‘varchar(255)’),
r.value(‘Date[1]’, ‘date’),
r.value(‘Time[1]’, ‘datetime’),
r.value(‘SubTotal[1]’, ‘decimal(18,2)’),
r.value(‘DiscountAmount[1]’, ‘decimal(18,2)’),
r.value(‘GrandTotalAmount[1]’, ‘decimal(18,2)’),
r.value(‘SettelementMode[1]’, ‘varchar(255)’),
r.value(‘TransactionStatus[1]’, ‘varchar(255)’)
FROM @xml.nodes(‘/Records/Record’) AS t(r);

INSERT INTO taxes (BillNo, TaxDesciption, TaxAmount)
SELECT r.value(‘BillNo[1]’, ‘varchar(255)’),
t.value(‘TaxDesciption[1]’, ‘varchar(255)’),
t.value(‘TaxAmount[1]’, ‘decimal(18,2)’)
FROM @xml.nodes(‘/Records/Record’) AS t(r)
CROSS APPLY t.r.nodes(‘Taxes/Tax’) AS x(t);

Calling

Imports System.Data.SqlClient

Module Module1
Sub Main()
Dim con As SqlConnection = New SqlConnection(“Data Source=(local);Initial Catalog=TestDB;Integrated Security=True”)
con.Open()
Dim cmd As SqlCommand = New SqlCommand(“sp_InsertXMLData”, con)
cmd.CommandType = CommandType.StoredProcedure
Dim xmlData As String = “NavaFP01000012022-12-1712:20:43 AM0.00.00.0S C 10 %0.0C.Gst 2.5 %0.0S.Gst 2.5 %0.0NcSale”
cmd.Parameters.AddWithValue(“@xmlData”, xmlData)
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine(reader(0).ToString())
End While
reader.Close()
con.Close()
End Sub
End Module

Alternate

CREATE PROCEDURE usp_InsertRecord (@xmlData XML)
AS
BEGIN
DECLARE @POSName VARCHAR(50)
DECLARE @BillNo VARCHAR(50)
DECLARE @Date DATE
DECLARE @Time TIME
DECLARE @SubTotal DECIMAL(18, 2)
DECLARE @DiscountAmount DECIMAL(18, 2)
DECLARE @GrandTotalAmount DECIMAL(18, 2)
DECLARE @SettelementMode VARCHAR(50)
DECLARE @TransactionStatus VARCHAR(50)

— extract values from the XML
SELECT @POSName = T.c.value(‘POSName[1]’, ‘VARCHAR(50)’),
@BillNo = T.c.value(‘BillNo[1]’, ‘VARCHAR(50)’),
@Date = T.c.value(‘Date[1]’, ‘DATE’),
@Time = T.c.value(‘Time[1]’, ‘TIME’),
@SubTotal = T.c.value(‘SubTotal[1]’, ‘DECIMAL(18, 2)’),
@DiscountAmount = T.c.value(‘DiscountAmount[1]’, ‘DECIMAL(18, 2)’),
@GrandTotalAmount = T.c.value(‘GrandTotalAmount[1]’, ‘DECIMAL(18, 2)’),
@SettelementMode = T.c.value(‘SettelementMode[1]’, ‘VARCHAR(50)’),
@TransactionStatus = T.c.value(‘TransactionStatus[1]’, ‘VARCHAR(50)’)
FROM @xmlData.nodes(‘/Record’) T(c)

— insert values into the Sales table
INSERT INTO Sales (POSName, BillNo, Date, Time, SubTotal, DiscountAmount, GrandTotalAmount, SettelementMode, TransactionStatus)
VALUES (@POSName, @BillNo, @Date, @Time, @SubTotal, @DiscountAmount, @GrandTotalAmount, @SettelementMode, @TransactionStatus)

DECLARE @TaxId INT
SELECT @TaxId = SCOPE_IDENTITY()

— insert values into the Taxes table
INSERT INTO Taxes (SalesId, TaxDesciption, TaxAmount)
SELECT @TaxId, T1.c1.value(‘TaxDesciption[1]’, ‘VARCHAR(50)’), T1.c1.value(‘TaxAmount[1]’, ‘DECIMAL(18, 2)’)
FROM @xmlData.nodes(‘/Record/Taxes/Tax’) T1(c1)
END