Ошибка при зацикливании и вставке данных XML в таблицы MS SQL Server с пространством имен XQuery

Описание: В настоящее время я работаю над задачей, которая включает в себя синтаксический анализ XML-документа и вставку его данных в таблицы SQL Server. XML-документ содержит информацию о счете, и мне нужно просмотреть каждый счет и вставить данные в две таблицы: EDIInboundInvoiceHeader и EDIInboundInvoiceLineItems. Однако я столкнулся с проблемой с объявлением пространства имен XQuery при попытке вставить данные. Проблема: При выполнении хранимой процедуры для вставки данных XML в таблицы возникает следующая ошибка:

Сообщение 2229, уровень 16, состояние 1, процедура InsertEDIInboundInvoiceNew, строка 60 [строка запуска пакета 8] XQuery [query()]: имя «ns0» не обозначает пространство имен.

Хранимая процедура приведена ниже

USE [IntegrationStaging]
GO

/****** Object:  StoredProcedure [dbo].[InsertEDIInboundInvoiceNew]    Script Date: 8/9/2023 11:15:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertEDIInboundInvoiceNew]
    @InvoiceStatus VARCHAR(50),
    @ProcessStatus VARCHAR(50) = NULL,
    @IntegrationProcess VARCHAR(50) = NULL,
    @InvoiceXML XML,
    @StatusMessage VARCHAR(100) OUTPUT
AS
BEGIN
    BEGIN TRY
    

        DECLARE @InvoiceID VARCHAR(50)
        DECLARE @BusinessUnit VARCHAR(50)
        DECLARE @InvoiceNumber VARCHAR(50)
        DECLARE @InvoiceAmount VARCHAR(100)
        DECLARE @InvoiceDate DATE
        DECLARE @SupplierName VARCHAR(100)
        DECLARE @SupplierNumber VARCHAR(50)
        DECLARE @SupplierSite VARCHAR(50)
        DECLARE @InvoiceCurrency VARCHAR(10)
        DECLARE @PaymentCurrency VARCHAR(10)
        DECLARE @ImportSet VARCHAR(50)
        DECLARE @InvoiceType VARCHAR(50)
        DECLARE @LegalEntity VARCHAR(50)
        DECLARE @CustomerTaxRegistrationNumber VARCHAR(50)
        DECLARE @PaymentTerm VARCHAR(50)
        DECLARE @TermsDate DATE
        DECLARE @GoodsReceivedDate DATE
        DECLARE @InvoiceReceiveDate DATE
        DECLARE @AccountingDate DATE
        DECLARE @PaymentMethod VARCHAR(50)
        DECLARE @PayGroup VARCHAR(50)
        DECLARE @LiabilityDistribution VARCHAR(50)
        DECLARE @CreationDate DATETIME
        DECLARE @Source VARCHAR(50)
        DECLARE @IntegrationName VARCHAR(50)
        DECLARE @Status VARCHAR(50)

        DECLARE @LineNumber INT
        DECLARE @LineType VARCHAR(50)
        DECLARE @Amount VARCHAR(100)
        DECLARE @InvoiceQuantity INT
        DECLARE @UnitPrice VARCHAR(100)
        DECLARE @UOM VARCHAR(50)
        DECLARE @Description VARCHAR(100)
        DECLARE @PONumber VARCHAR(50)
        DECLARE @DistributionCombination VARCHAR(50)
        DECLARE @DistributionSet VARCHAR(50)
        DECLARE @TaxControlAmount VARCHAR(100)
        DECLARE @TaxRegimeCode VARCHAR(50)
        DECLARE @Tax VARCHAR(100)
        DECLARE @TaxStatusCode VARCHAR(50)
        DECLARE @TaxJurisdictionCode VARCHAR(50)
        DECLARE @TaxRateCode VARCHAR(50)
        DECLARE @TaxRate VARCHAR(50)
        DECLARE @WithholdingTaxGroup VARCHAR(50)
                -- Iterate through each invoice in the XML
        DECLARE @InvoiceNodes XML
        
        SET @InvoiceNodes = @InvoiceXML.query('/ns0:InvoiceCanonical')

        DECLARE @InvoiceCount INT
        
        SELECT @InvoiceCount = @InvoiceNodes.value('count(/ns0:InvoiceCanonical)', 'INT')

        DECLARE @CurrentInvoice INT = 1

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

            -- Insert data into EDIInboundInvoiceHeader
            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
            )
            VALUES (
                @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
            )

            DECLARE @InvoiceHeaderID INT
            SET @InvoiceHeaderID = SCOPE_IDENTITY()

            -- Insert data into EDIInboundInvoiceLineItems
            INSERT INTO EDIInboundInvoiceLineItems (InvoiceHeaderID, LineNumber, LineType, Amount, InvoiceQuantity, UnitPrice, UOM, Description, PONumber, DistributionCombination, DistributionSet, AccountingDate, TaxControlAmount, TaxRegimeCode, Tax, TaxStatusCode, TaxJurisdictionCode, TaxRateCode, TaxRate, WithholdingTaxGroup)
            SELECT
                @InvoiceHeaderID,
                InvoiceItem.value('(ns0:LineNumber)[1]', 'INT'),
                InvoiceItem.value('(ns0:LineType)[1]', 'VARCHAR(50)'),
                -- ... other attribute extractions ...
                InvoiceItem.value('(ns0:Amount)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:InvoiceQuantity)[1]', 'INT'),
                InvoiceItem.value('(ns0:UnitPrice)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:UOM)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:Description)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:PONumber)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:DistributionCombination)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:DistributionSet)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:AccountingDate)[1]', 'DATE'),
                InvoiceItem.value('(ns0:TaxControlAmount)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:TaxRegimeCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:Tax)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:TaxStatusCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxJurisdictionCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxRateCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxRate)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:WithholdingTaxGroup)[1]', 'VARCHAR(50)')
            FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[' + CAST(@CurrentInvoice AS VARCHAR) + ']/ns0:Detail') AS InvoiceItem(InvoiceItem)

            SET @CurrentInvoice = @CurrentInvoice + 1
        END

        SET @StatusMessage = 'Success' -- Output message on success
    END TRY
    BEGIN CATCH
        SET @StatusMessage = ERROR_MESSAGE() -- Return error message on failure
    END CATCH
END

Ввод XML приведен ниже

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

Я ожидаю, что хранимая процедура будет успешно перебирать каждый счет в XML-документе, извлекать необходимые атрибуты и вставлять их в соответствующие таблицы SQL Server. Пространство имен XQuery должно быть правильно распознано, чтобы избежать упомянутой выше ошибки.

Привет Ицхак, Пожалуйста, найдите измененный код в соответствии с вашим предложением.

USE [IntegrationStaging]
GO

/****** Object:  StoredProcedure [dbo].[InsertEDIInboundInvoiceNew]    Script Date: 8/9/2023 11:15:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertEDIInboundInvoiceNew]
    @InvoiceStatus VARCHAR(50),
    @ProcessStatus VARCHAR(50) = NULL,
    @IntegrationProcess VARCHAR(50) = NULL,
    @InvoiceXML XML,
    @StatusMessage VARCHAR(100) OUTPUT
AS
BEGIN
    BEGIN TRY
    

        DECLARE @InvoiceID VARCHAR(50)
        DECLARE @BusinessUnit VARCHAR(50)
        DECLARE @InvoiceNumber VARCHAR(50)
        DECLARE @InvoiceAmount VARCHAR(100)
        DECLARE @InvoiceDate DATE
        DECLARE @SupplierName VARCHAR(100)
        DECLARE @SupplierNumber VARCHAR(50)
        DECLARE @SupplierSite VARCHAR(50)
        DECLARE @InvoiceCurrency VARCHAR(10)
        DECLARE @PaymentCurrency VARCHAR(10)
        DECLARE @ImportSet VARCHAR(50)
        DECLARE @InvoiceType VARCHAR(50)
        DECLARE @LegalEntity VARCHAR(50)
        DECLARE @CustomerTaxRegistrationNumber VARCHAR(50)
        DECLARE @PaymentTerm VARCHAR(50)
        DECLARE @TermsDate DATE
        DECLARE @GoodsReceivedDate DATE
        DECLARE @InvoiceReceiveDate DATE
        DECLARE @AccountingDate DATE
        DECLARE @PaymentMethod VARCHAR(50)
        DECLARE @PayGroup VARCHAR(50)
        DECLARE @LiabilityDistribution VARCHAR(50)
        DECLARE @CreationDate DATETIME
        DECLARE @Source VARCHAR(50)
        DECLARE @IntegrationName VARCHAR(50)
        DECLARE @Status VARCHAR(50)

        DECLARE @LineNumber INT
        DECLARE @LineType VARCHAR(50)
        DECLARE @Amount VARCHAR(100)
        DECLARE @InvoiceQuantity INT
        DECLARE @UnitPrice VARCHAR(100)
        DECLARE @UOM VARCHAR(50)
        DECLARE @Description VARCHAR(100)
        DECLARE @PONumber VARCHAR(50)
        DECLARE @DistributionCombination VARCHAR(50)
        DECLARE @DistributionSet VARCHAR(50)
        DECLARE @TaxControlAmount VARCHAR(100)
        DECLARE @TaxRegimeCode VARCHAR(50)
        DECLARE @Tax VARCHAR(100)
        DECLARE @TaxStatusCode VARCHAR(50)
        DECLARE @TaxJurisdictionCode VARCHAR(50)
        DECLARE @TaxRateCode VARCHAR(50)
        DECLARE @TaxRate VARCHAR(50)
        DECLARE @WithholdingTaxGroup VARCHAR(50)
                -- Iterate through each invoice in the XML
        DECLARE @InvoiceNodes XML
        SET @InvoiceNodes = @InvoiceXML.query('declare namespace ns0 = "http://Invoice.Common.Schemas.InvoiceCanonical";
            /ns0:InvoiceCanonical')

        DECLARE @InvoiceCount INT
        
        SELECT @InvoiceCount = @InvoiceNodes.value('declare namespace ns0 = "http://Invoice.Common.Schemas.InvoiceCanonical";
            count(/ns0:InvoiceCanonical)', 'INT')


        DECLARE @CurrentInvoice INT = 1

        WHILE @CurrentInvoice <= @InvoiceCount
        BEGIN
            WITH XMLNAMESPACES('http://Invoice.Common.Schemas.InvoiceCanonical' AS ns0)
            SELECT
                @InvoiceID = InvoiceHeader.value('(ns0:InvoiceID)[1]', 'VARCHAR(50)'),
                @BusinessUnit = InvoiceHeader.value('(ns0:BusinessUnit)[1]', 'VARCHAR(50)'),
                @InvoiceNumber = InvoiceHeader.value('(ns0:InvoiceNumber)[1]', 'VARCHAR(50)'),
                @InvoiceAmount = InvoiceHeader.value('(ns0:InvoiceAmount)[1]', 'VARCHAR(100)'),
                @InvoiceDate = InvoiceHeader.value('(ns0:InvoiceDate)[1]', 'DATE'),
                @SupplierName = InvoiceHeader.value('(ns0:SupplierName)[1]', 'VARCHAR(100)'),
                @SupplierNumber = InvoiceHeader.value('(ns0:SupplierNumber)[1]', 'VARCHAR(50)'),
                @SupplierSite = InvoiceHeader.value('(ns0:SupplierSite)[1]', 'VARCHAR(50)'),
                @InvoiceCurrency = InvoiceHeader.value('(ns0:InvoiceCurrency)[1]', 'VARCHAR(10)'),
                @PaymentCurrency = InvoiceHeader.value('(ns0:PaymentCurrency)[1]', 'VARCHAR(10)'),
                @ImportSet = InvoiceHeader.value('(ns0:ImportSet)[1]', 'VARCHAR(50)'),
                @InvoiceType = InvoiceHeader.value('(ns0:InvoiceType)[1]', 'VARCHAR(50)'),
                @LegalEntity = InvoiceHeader.value('(ns0:LegalEntity)[1]', 'VARCHAR(50)'),
                @CustomerTaxRegistrationNumber = InvoiceHeader.value('(ns0:CustomerTaxRegistrationNumber)[1]', 'VARCHAR(50)'),
                @PaymentTerm = InvoiceHeader.value('(ns0:PaymentTerm)[1]', 'VARCHAR(50)'),
                @TermsDate = InvoiceHeader.value('(ns0:TermsDate)[1]', 'DATE'),
                @GoodsReceivedDate = InvoiceHeader.value('(ns0:GoodsReceivedDate)[1]', 'DATE'),
                @InvoiceReceiveDate = InvoiceHeader.value('(ns0:InvoiceReceiveDate)[1]', 'DATE'),
                @AccountingDate = InvoiceHeader.value('(ns0:AccountingDate)[1]', 'DATE'),
                @PaymentMethod = InvoiceHeader.value('(ns0:PaymentMethod)[1]', 'VARCHAR(50)'),
                @PayGroup = InvoiceHeader.value('(ns0:PayGroup)[1]', 'VARCHAR(50)'),
                @LiabilityDistribution = InvoiceHeader.value('(ns0:LiabilityDistribution)[1]', 'VARCHAR(50)'),
                @CreationDate = InvoiceHeader.value('(ns0:CreationDate)[1]', 'DATETIME'),
                @Source = InvoiceHeader.value('(ns0:Source)[1]', 'VARCHAR(50)'),
                @IntegrationName = InvoiceHeader.value('(ns0:IntegrationName)[1]', 'VARCHAR(50)'),
                @Status = InvoiceHeader.value('(ns0:Status)[1]', 'VARCHAR(50)')
            FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@CurrentInvoice")]/ns0:Header') AS InvoiceHeader(InvoiceHeader)

            -- Insert data into EDIInboundInvoiceHeader
            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
            )
            VALUES (
                @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
            )

            DECLARE @InvoiceHeaderID INT
            SET @InvoiceHeaderID = SCOPE_IDENTITY()

            -- Insert data into EDIInboundInvoiceLineItems
            INSERT INTO EDIInboundInvoiceLineItems (InvoiceHeaderID, LineNumber, LineType, Amount, InvoiceQuantity, UnitPrice, UOM, Description, PONumber, DistributionCombination, DistributionSet, AccountingDate, TaxControlAmount, TaxRegimeCode, Tax, TaxStatusCode, TaxJurisdictionCodeTaxJurisdictionCode, TaxRateCode, TaxRate, WithholdingTaxGroup)
            
            SELECT
                @InvoiceHeaderID,
                InvoiceItem.value('(ns0:LineNumber)[1]', 'INT'),
                InvoiceItem.value('(ns0:LineType)[1]', 'VARCHAR(50)'),
                -- ... other attribute extractions ...
                InvoiceItem.value('(ns0:Amount)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:InvoiceQuantity)[1]', 'INT'),
                InvoiceItem.value('(ns0:UnitPrice)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:UOM)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:Description)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:PONumber)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:DistributionCombination)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:DistributionSet)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:AccountingDate)[1]', 'DATE'),
                InvoiceItem.value('(ns0:TaxControlAmount)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:TaxRegimeCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:Tax)[1]', 'VARCHAR(100)'),
                InvoiceItem.value('(ns0:TaxStatusCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxJurisdictionCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxRateCode)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:TaxRate)[1]', 'VARCHAR(50)'),
                InvoiceItem.value('(ns0:WithholdingTaxGroup)[1]', 'VARCHAR(50)')
            FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@CurrentInvoice")]/ns0:Detail') AS InvoiceItem(InvoiceItem)

            SET @CurrentInvoice = @CurrentInvoice + 1
        END

        SET @StatusMessage = 'Success' -- Output message on success
    END TRY
    BEGIN CATCH
        SET @StatusMessage = ERROR_MESSAGE() -- Return error message on failure
    END CATCH
END
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
0
53
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вам необходимо объявить пространства имен, прежде чем вы сможете запрашивать с ними XML.

Посмотрите упрощенную версию.

SQL

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.EDIInboundInvoiceLineItems;
DROP TABLE IF EXISTS dbo.EDIInboundInvoiceHeader;

CREATE TABLE dbo.EDIInboundInvoiceHeader (
    InvoiceHeaderID INT IDENTITY PRIMARY KEY,
    InvoiceID VARCHAR(20),
    BusinessUnit VARCHAR(20),
    InvoiceNumber VARCHAR(20)
);
CREATE TABLE dbo.EDIInboundInvoiceLineItems (
    InvoiceHeaderID INT NOT NULL FOREIGN KEY REFERENCES dbo.EDIInboundInvoiceHeader(InvoiceHeaderID), 
    LineNumber INT, 
    LineType VARCHAR(20), 
    Amount MONEY, 
    InvoiceQuantity INT
);
-- DDL and sample data population, end

DECLARE @InvoiceXML 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>';

DECLARE @InvoiceCount INT = (
    SELECT @InvoiceXML.value('declare namespace ns0 = "namespace_URL";
        count(/ns0:InvoiceCanonical)', 'INT'));

DECLARE @InvoiceHeaderID INT
    , @CurrentInvoice INT = 1;

WHILE @CurrentInvoice <= @InvoiceCount 
BEGIN
    -- Insert data into EDIInboundInvoiceHeader table
    WITH XMLNAMESPACES('namespace_URL' AS ns0)
    INSERT INTO dbo.EDIInboundInvoiceHeader (InvoiceID, BusinessUnit, InvoiceNumber )
    SELECT InvoiceHeader.value('(InvoiceID/text())[1]', 'VARCHAR(20)')
        , InvoiceHeader.value('(BusinessUnit/text())[1]', 'VARCHAR(20)')
        , InvoiceHeader.value('(InvoiceNumber/text())[1]', 'VARCHAR(20)')
        -- add the rest of the XML elements
    FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@CurrentInvoice")]/Header') AS InvoiceHeader(InvoiceHeader);

    SET @InvoiceHeaderID = SCOPE_IDENTITY();

    -- Insert data into EDIInboundInvoiceLineItems table
    WITH XMLNAMESPACES('namespace_URL' AS ns0)
    INSERT INTO dbo.EDIInboundInvoiceLineItems (InvoiceHeaderID, LineNumber, LineType, Amount, InvoiceQuantity)
    SELECT @InvoiceHeaderID
        , InvoiceItem.value('(LineNumber/text())[1]', 'INT')
        , InvoiceItem.value('(LineType/text())[1]', 'VARCHAR(20)')
        , InvoiceItem.value('(Amount/text())[1]', 'MONEY')
        , InvoiceItem.value('(InvoiceQuantity/text())[1]', 'INT')
        -- add the rest of the XML elements
    FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@CurrentInvoice")]/Detail') AS InvoiceItem(InvoiceItem);

    SET @CurrentInvoice += 1;
END;

-- test
SELECT * FROM dbo.EDIInboundInvoiceHeader;
SELECT * FROM dbo.EDIInboundInvoiceLineItems;

EDIInboundInvoiceHeader

Идентификатор заголовка счета-фактуры Идентификатор счета Бизнес-единица Номер счета 1 INV0001 БУ123 ИНВ123456 2 INV0002 БУ456 ИНВ789012

EDIInboundInvoiceLineItems

Идентификатор заголовка счета-фактуры Номер строки Тип линии Количество СчетКоличество 1 1 Элемент 500.00 10 1 2 Элемент 800.00 5 1 3 Элемент 300.00 3 2 1 Элемент 800.00 8

Привет, Ицхак, я пробовал это, но получаю ту же ошибку: Msg 2229, уровень 16, состояние 1, процедура InsertEDIInboundInvoiceNew, строка 150 [строка запуска партии 8] XQuery [nodes()]: имя «ns0» не обозначает пространство имен.

Vipin Jayanarayanan 10.08.2023 11:51

У вас есть два уровня: InvoiceHeader и InvoiceLineItems. Итак, вам нужно применить исправления, которые я показал, на обоих уровнях.

Yitzhak Khabinsky 10.08.2023 14:11

Привет, Ицхак, я внес изменения на обоих уровнях. entInvoice")]/ns0:He‌​ader') AS InvoiceHeader(InvoiceHeader) FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@Curr‌​entInvoice")]/ns0:De‌​tail') AS ИнвойсЭлемент (ИнвойсЭлемент)

Vipin Jayanarayanan 10.08.2023 14:57

Пожалуйста, добавьте свой T-SQL с исправлениями к исходному вопросу.

Yitzhak Khabinsky 10.08.2023 15:00

Привет @YitzhakKhabinsky, я добавил измененный код в исходный вопрос.

Vipin Jayanarayanan 10.08.2023 17:04

Я обновил ответ. Пожалуйста, попробуйте.

Yitzhak Khabinsky 10.08.2023 17:55

Привет @Yitzhak, теперь я могу выполнить хранимую процедуру с вашим обновленным кодом. Однако только таблица [EDIInboundInvoiceHeader] частично обновляется с помощью InvoiceHeaderID, InvoiceStatus, ProcessStatus, IntegrationProcess. Остальные поля обновляются как нулевые, чего быть не должно. В [dbo] не было вставлено ни одной записи.[EDIInboundInvoiceLineItems]

Vipin Jayanarayanan 10.08.2023 18:32

Пожалуйста, добавьте к исходному вопросу оба оператора CREATE TABLE... и все значения входных параметров SP.

Yitzhak Khabinsky 10.08.2023 19:50

Только корневой узел нуждается в пространстве имен

Charlieface 10.08.2023 21:47

@VipinJayanarayanan, я дал ответ, следуя минимальному воспроизводимому шаблону примера. Вы копируете его в SSMS как есть, запускаете, и он работает.

Yitzhak Khabinsky 10.08.2023 22:13

@YitzhakKhabinsky, новый код работает нормально. Большое спасибо. Очень признателен.

Vipin Jayanarayanan 11.08.2023 16:04

Как уже упоминалось, вам нужно указать пространство имен в файле WITH XMLNAMESPACES. Но это только первый узел InvoiceCanonical, который нуждается в указании, остальные находятся в пустом пространстве имен.

  • Вы можете еще больше улучшить этот код, вместо использования курсора, вы просто OUTPUT inserted.InvoiceHeaderID INTO @tableVariable, а затем сопоставляете его в другом INSERT запросе.
  • Вам также не нужен отдельный SELECT, так как вы можете сделать еще один OUTPUT, чтобы выбрать все обратно клиенту.
  • Не используйте этот ужасный TRY CATCH вместо этого просто позвольте ошибкам возвращаться к клиенту должным образом.
  • Используйте /text() для повышения производительности в вашем XQuery.
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)');

дб<>рабочий пример

Привет @Charlieface, я попытался запустить эту хранимую процедуру, которой вы поделились с пространством имен по умолчанию. Он не вставлял никаких записей.

Vipin Jayanarayanan 10.08.2023 17:07

Только что понял, что пространство имен нужно только корневому узлу, см. редактирование. Также смотрите скрипку dbfiddle.uk/EVOLWrSx

Charlieface 10.08.2023 21:46

Другие вопросы по теме