SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EDIInboundInvoiceHeader (
  InvoiceHeaderID int identity primary key,
  InvoiceID varchar(50) not null
);
CREATE OR ALTER PROCEDURE [dbo].[InsertEDIInboundInvoiceNew]
    --@InvoiceStatus VARCHAR(50),
    @ProcessStatus VARCHAR(50) = NULL,
    @IntegrationProcess VARCHAR(50) = NULL,
    @InvoiceXML XML
AS

DECLARE @ids TABLE (InvoiceID varchar(50) PRIMARY KEY, InvoiceHeaderID int not null);

WITH XMLNAMESPACES(
    'namespace_URL' AS ns0
)
INSERT INTO EDIInboundInvoiceHeader (
--  InvoiceStatus, ProcessStatus, IntegrationProcess,
  InvoiceID
--  ,BusinessUnit, InvoiceNumber, InvoiceAmount, InvoiceDate,
--  SupplierName, SupplierNumber, SupplierSite, InvoiceCurrency,
--  PaymentCurrency, ImportSet, InvoiceType, LegalEntity,
--  CustomerTaxRegistrationNumber, PaymentTerm, TermsDate,
--  GoodsReceivedDate, InvoiceReceiveDate, AccountingDate,
--  PaymentMethod, PayGroup, LiabilityDistribution, CreationDate,
--  Source, IntegrationName, Status
)

OUTPUT inserted.InvoiceID, inserted.InvoiceHeaderID INTO @ids (InvoiceID, InvoiceHeaderID)
OUTPUT inserted.*

SELECT
/*  @InvoiceStatus,
  @ProcessStatus,
  @IntegrationProcess, */
  InvoiceID = InvoiceHeader.value('(InvoiceID/text())[1]', 'VARCHAR(50)')
  /*,
  BusinessUnit = InvoiceHeader.value('(BusinessUnit/text())[1]', 'VARCHAR(50)'),
  InvoiceNumber = InvoiceHeader.value('(InvoiceNumber/text())[1]', 'VARCHAR(50)'),
  InvoiceAmount = InvoiceHeader.value('(InvoiceAmount/text())[1]', 'VARCHAR(100)'),
  InvoiceDate = InvoiceHeader.value('(InvoiceDate/text())[1]', 'DATE'),
  SupplierName = InvoiceHeader.value('(SupplierName/text())[1]', 'VARCHAR(100)'),
  SupplierNumber = InvoiceHeader.value('(SupplierNumber/text())[1]', 'VARCHAR(50)'),
  SupplierSite = InvoiceHeader.value('(SupplierSite/text())[1]', 'VARCHAR(50)'),
  InvoiceCurrency = InvoiceHeader.value('(InvoiceCurrency/text())[1]', 'VARCHAR(10)'),
  PaymentCurrency = InvoiceHeader.value('(PaymentCurrency/text())[1]', 'VARCHAR(10)'),
  ImportSet = InvoiceHeader.value('(ImportSet/text())[1]', 'VARCHAR(50)'),
  InvoiceType = InvoiceHeader.value('(InvoiceType/text())[1]', 'VARCHAR(50)'),
  LegalEntity = InvoiceHeader.value('(LegalEntity/text())[1]', 'VARCHAR(50)'),
  CustomerTaxRegistrationNumber = InvoiceHeader.value('(CustomerTaxRegistrationNumber/text())[1]', 'VARCHAR(50)'),
  PaymentTerm = InvoiceHeader.value('(PaymentTerm/text())[1]', 'VARCHAR(50)'),
  TermsDate = InvoiceHeader.value('(TermsDate/text())[1]', 'DATE'),
  GoodsReceivedDate = InvoiceHeader.value('(GoodsReceivedDate/text())[1]', 'DATE'),
  InvoiceReceiveDate = InvoiceHeader.value('(InvoiceReceiveDate/text())[1]', 'DATE'),
  AccountingDate = InvoiceHeader.value('(AccountingDate/text())[1]', 'DATE'),
  PaymentMethod = InvoiceHeader.value('(PaymentMethod/text())[1]', 'VARCHAR(50)'),
  PayGroup = InvoiceHeader.value('(PayGroup/text())[1]', 'VARCHAR(50)'),
  LiabilityDistribution = InvoiceHeader.value('(LiabilityDistribution/text())[1]', 'VARCHAR(50)'),
  CreationDate = InvoiceHeader.value('(CreationDate/text())[1]', 'DATETIME'),
  Source = InvoiceHeader.value('(Source/text())[1]', 'VARCHAR(50)'),
  IntegrationName = InvoiceHeader.value('(IntegrationName/text())[1]', 'VARCHAR(50)'),
  Status = InvoiceHeader.value('(Status/text())[1]', 'VARCHAR(50)')
*/
FROM @InvoiceXML.nodes('ns0:InvoiceCanonical/Header') AS InvoiceHeader(InvoiceHeader);


WITH XMLNAMESPACES(
    'namespace_URL' AS ns0
)
--INSERT INTO EDIInboundInvoiceLineItems (
--    InvoiceHeaderID, LineNumber, LineType, Amount, InvoiceQuantity, UnitPrice, UOM, Description,
--    PONumber, DistributionCombination, DistributionSet, AccountingDate, TaxControlAmount, TaxRegimeCode,
--    Tax, TaxStatusCode, TaxJurisdictionCode, TaxRateCode, TaxRate, WithholdingTaxGroup
--)
SELECT
  ids.InvoiceHeaderID,
  InvoiceItem.value('(LineNumber/text())[1]', 'INT'),
  InvoiceItem.value('(LineType/text())[1]', 'VARCHAR(50)'),
  -- ... other attribute extractions ...
  InvoiceItem.value('(Amount/text())[1]', 'VARCHAR(100)'),
  InvoiceItem.value('(InvoiceQuantity/text())[1]', 'INT'),
  InvoiceItem.value('(UnitPrice/text())[1]', 'VARCHAR(100)'),
  InvoiceItem.value('(UOM/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(Description/text())[1]', 'VARCHAR(100)'),
  InvoiceItem.value('(PONumber/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(DistributionCombination/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(DistributionSet/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(AccountingDate/text())[1]', 'DATE'),
  InvoiceItem.value('(TaxControlAmount/text())[1]', 'VARCHAR(100)'),
  InvoiceItem.value('(TaxRegimeCode/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(Tax/text())[1]', 'VARCHAR(100)'),
  InvoiceItem.value('(TaxStatusCode/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(TaxJurisdictionCode/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(TaxRateCode/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(TaxRate/text())[1]', 'VARCHAR(50)'),
  InvoiceItem.value('(WithholdingTaxGroup/text())[1]', 'VARCHAR(50)')
FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical/Detail') AS InvoiceItem(InvoiceItem)
JOIN @ids ids ON ids.InvoiceID = InvoiceItem.value('(InvoiceID/text())[1]', 'VARCHAR(50)');

DECLARE @xml xml = N'<ns0:InvoiceCanonical xmlns:ns0="namespace_URL">
  <Header xmlns:ns0="namespace_URL">
    <InvoiceID>INV0001</InvoiceID>
    <BusinessUnit>BU123</BusinessUnit>
    <InvoiceNumber>INV123456</InvoiceNumber>
    <InvoiceAmount>1500.00</InvoiceAmount>
    <InvoiceDate>2023-08-01</InvoiceDate>
    <SupplierName>ABC Supplier</SupplierName>
    <SupplierNumber>SUP001</SupplierNumber>
    <SupplierSite>Main Site</SupplierSite>
    <InvoiceCurrency>USD</InvoiceCurrency>
    <PaymentCurrency>USD</PaymentCurrency>
    <ImportSet>ImportSet_123</ImportSet>
    <InvoiceType>Regular</InvoiceType>
    <LegalEntity>LE001</LegalEntity>
    <CustomerTaxRegistrationNumber>1234567890</CustomerTaxRegistrationNumber>
    <PaymentTerm>Net 30</PaymentTerm>
    <TermsDate>2023-09-01</TermsDate>
    <GoodsReceivedDate>2023-07-25</GoodsReceivedDate>
    <InvoiceReceiveDate>2023-08-02</InvoiceReceiveDate>
    <AccountingDate>2023-08-02</AccountingDate>
    <PaymentMethod>Bank Transfer</PaymentMethod>
    <PayGroup>PG001</PayGroup>
    <LiabilityDistribution>LD001</LiabilityDistribution>
    <CreationDate>2023-08-01T08:00:00</CreationDate>
    <Source>System A</Source>
    <IntegrationName>Integration_ABC</IntegrationName>
    <Status>Approved</Status>
  </Header>
  <Detail xmlns:ns0="namespace_URL">
    <InvoiceID>INV0001</InvoiceID>
    <LineNumber>1</LineNumber>
    <LineType>Item</LineType>
    <Amount>500.00</Amount>
    <InvoiceQuantity>10</InvoiceQuantity>
    <UnitPrice>50.00</UnitPrice>
    <UOM>EA</UOM>
    <Description>Product A</Description>
    <PONumber>PO1234</PONumber>
    <DistributionCombination>DC123</DistributionCombination>
    <DistributionSet>DS001</DistributionSet>
    <AccountingDate>2023-08-02</AccountingDate>
    <TaxControlAmount>50.00</TaxControlAmount>
    <TaxRegimeCode>TAXREG001</TaxRegimeCode>
    <Tax>10.00</Tax>
    <TaxStatusCode>TAXSTAT001</TaxStatusCode>
    <TaxJurisdictionCodeTaxJurisdictionCode>TAXJUR001</TaxJurisdictionCodeTaxJurisdictionCode>
    <TaxRateCode>TAXRATE001</TaxRateCode>
    <TaxRate>10%</TaxRate>
    <WithholdingTaxGroup>WTG001</WithholdingTaxGroup>
  </Detail>
  <Detail xmlns:ns0="namespace_URL">
    <InvoiceID>INV0001</InvoiceID>
    <LineNumber>2</LineNumber>
    <LineType>Item</LineType>
    <Amount>800.00</Amount>
    <InvoiceQuantity>5</InvoiceQuantity>
    <UnitPrice>160.00</UnitPrice>
    <UOM>EA</UOM>
    <Description>Product B</Description>
    <PONumber>PO5678</PONumber>
    <DistributionCombination>DC456</DistributionCombination>
    <DistributionSet>DS002</DistributionSet>
    <AccountingDate>2023-08-03</AccountingDate>
    <TaxControlAmount>80.00</TaxControlAmount>
    <TaxRegimeCode>TAXREG002</TaxRegimeCode>
    <Tax>16.00</Tax>
    <TaxStatusCode>TAXSTAT002</TaxStatusCode>
    <TaxJurisdictionCodeTaxJurisdictionCode>TAXJUR002</TaxJurisdictionCodeTaxJurisdictionCode>
    <TaxRateCode>TAXRATE002</TaxRateCode>
    <TaxRate>20%</TaxRate>
    <WithholdingTaxGroup>WTG002</WithholdingTaxGroup>
  </Detail>
  <Detail xmlns:ns0="namespace_URL">
    <InvoiceID>INV0001</InvoiceID>
    <LineNumber>3</LineNumber>
    <LineType>Item</LineType>
    <Amount>300.00</Amount>
    <InvoiceQuantity>3</InvoiceQuantity>
    <UnitPrice>100.00</UnitPrice>
    <UOM>EA</UOM>
    <Description>Product C</Description>
    <PONumber>PO9090</PONumber>
    <DistributionCombination>DC789</DistributionCombination>
    <DistributionSet>DS003</DistributionSet>
    <AccountingDate>2023-08-03</AccountingDate>
    <TaxControlAmount>30.00</TaxControlAmount>
    <TaxRegimeCode>TAXREG003</TaxRegimeCode>
    <Tax>9.00</Tax>
    <TaxStatusCode>TAXSTAT003</TaxStatusCode>
    <TaxJurisdictionCodeTaxJurisdictionCode>TAXJUR003</TaxJurisdictionCodeTaxJurisdictionCode>
    <TaxRateCode>TAXRATE003</TaxRateCode>
    <TaxRate>9%</TaxRate>
    <WithholdingTaxGroup>WTG003</WithholdingTaxGroup>
  </Detail>
</ns0:InvoiceCanonical>
<ns0:InvoiceCanonical xmlns:ns0="namespace_URL">
  <Header xmlns:ns0="http://Invoice.Common.Schemas.InvoiceCanonical">
    <InvoiceID>INV0002</InvoiceID>
    <BusinessUnit>BU456</BusinessUnit>
    <InvoiceNumber>INV789012</InvoiceNumber>
    <InvoiceAmount>2500.00</InvoiceAmount>
    <InvoiceDate>2023-08-05</InvoiceDate>
    <SupplierName>XYZ Suppliers</SupplierName>
    <SupplierNumber>SUP002</SupplierNumber>
    <SupplierSite>Secondary Site</SupplierSite>
    <InvoiceCurrency>EUR</InvoiceCurrency>
    <PaymentCurrency>EUR</PaymentCurrency>
    <ImportSet>ImportSet_456</ImportSet>
    <InvoiceType>Special</InvoiceType>
    <LegalEntity>LE002</LegalEntity>
    <CustomerTaxRegistrationNumber>9876543210</CustomerTaxRegistrationNumber>
    <PaymentTerm>Net 45</PaymentTerm>
    <TermsDate>2023-09-15</TermsDate>
    <GoodsReceivedDate>2023-08-01</GoodsReceivedDate>
    <InvoiceReceiveDate>2023-08-06</InvoiceReceiveDate>
    <AccountingDate>2023-08-06</AccountingDate>
    <PaymentMethod>Credit Card</PaymentMethod>
    <PayGroup>PG002</PayGroup>
    <LiabilityDistribution>LD002</LiabilityDistribution>
    <CreationDate>2023-08-05T09:30:00</CreationDate>
    <Source>System B</Source>
    <IntegrationName>Integration_XYZ</IntegrationName>
    <Status>Pending Approval</Status>
  </Header>
  <Detail xmlns:ns0="namespace_URL">
    <InvoiceID>INV0002</InvoiceID>
    <LineNumber>1</LineNumber>
    <LineType>Item</LineType>
    <Amount>800.00</Amount>
    <InvoiceQuantity>8</InvoiceQuantity>
    <UnitPrice>100.00</UnitPrice>
    <UOM>EA</UOM>
    <Description>Product X</Description>
    <PONumber>PO9876</PONumber>
    <DistributionCombination>DC987</DistributionCombination>
    <DistributionSet>DS004</DistributionSet>
    <AccountingDate>2023-08-06</AccountingDate>
    <TaxControlAmount>80.00</TaxControlAmount>
    <TaxRegimeCode>TAXREG004</TaxRegimeCode>
    <Tax>10.00</Tax>
    <TaxStatusCode>TAXSTAT004</TaxStatusCode>
    <TaxJurisdictionCodeTaxJurisdictionCode>TAXJUR004</TaxJurisdictionCodeTaxJurisdictionCode>
    <TaxRateCode>TAXRATE004</TaxRateCode>
    <TaxRate>12.5%</TaxRate>
    <WithholdingTaxGroup>WTG004</WithholdingTaxGroup>
  </Detail>
</ns0:InvoiceCanonical>
';

EXEC InsertEDIInboundInvoiceNew @InvoiceXML = @xml;


InvoiceHeaderID
InvoiceID


1
INV0001

2
INV0002




InvoiceHeaderID
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)
(No column name)


1
1
Item
500.00
10
50.00
EA
Product A
PO1234
DC123
DS001
2023-08-02
50.00
TAXREG001
10.00
TAXSTAT001
null
TAXRATE001
10%
WTG001

1
2
Item
800.00
5
160.00
EA
Product B
PO5678
DC456
DS002
2023-08-03
80.00
TAXREG002
16.00
TAXSTAT002
null
TAXRATE002
20%
WTG002

1
3
Item
300.00
3
100.00
EA
Product C
PO9090
DC789
DS003
2023-08-03
30.00
TAXREG003
9.00
TAXSTAT003
null
TAXRATE003
9%
WTG003

2
1
Item
800.00
8
100.00
EA
Product X
PO9876
DC987
DS004
2023-08-06
80.00
TAXREG004
10.00
TAXSTAT004
null
TAXRATE004
12.5%
WTG004
InvoiceHeaderID	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)
1	1	Item	500.00	10	50.00	EA	Product A	PO1234	DC123	DS001	2023-08-02	50.00	TAXREG001	10.00	TAXSTAT001	null	TAXRATE001	10%	WTG001
1	2	Item	800.00	5	160.00	EA	Product B	PO5678	DC456	DS002	2023-08-03	80.00	TAXREG002	16.00	TAXSTAT002	null	TAXRATE002	20%	WTG002
1	3	Item	300.00	3	100.00	EA	Product C	PO9090	DC789	DS003	2023-08-03	30.00	TAXREG003	9.00	TAXSTAT003	null	TAXRATE003	9%	WTG003
2	1	Item	800.00	8	100.00	EA	Product X	PO9876	DC987	DS004	2023-08-06	80.00	TAXREG004	10.00	TAXSTAT004	null	TAXRATE004	12.5%	WTG004