Описание: В настоящее время я работаю над задачей, которая включает в себя синтаксический анализ 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
Вам необходимо объявить пространства имен, прежде чем вы сможете запрашивать с ними 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
EDIInboundInvoiceLineItems
У вас есть два уровня: InvoiceHeader и InvoiceLineItems. Итак, вам нужно применить исправления, которые я показал, на обоих уровнях.
Привет, Ицхак, я внес изменения на обоих уровнях. entInvoice")]/ns0:Header') AS InvoiceHeader(InvoiceHeader) FROM @InvoiceXML.nodes('/ns0:InvoiceCanonical[sql:variable("@CurrentInvoice")]/ns0:Detail') AS ИнвойсЭлемент (ИнвойсЭлемент)
Пожалуйста, добавьте свой T-SQL с исправлениями к исходному вопросу.
Привет @YitzhakKhabinsky, я добавил измененный код в исходный вопрос.
Я обновил ответ. Пожалуйста, попробуйте.
Привет @Yitzhak, теперь я могу выполнить хранимую процедуру с вашим обновленным кодом. Однако только таблица [EDIInboundInvoiceHeader] частично обновляется с помощью InvoiceHeaderID, InvoiceStatus, ProcessStatus, IntegrationProcess. Остальные поля обновляются как нулевые, чего быть не должно. В [dbo] не было вставлено ни одной записи.[EDIInboundInvoiceLineItems]
Пожалуйста, добавьте к исходному вопросу оба оператора CREATE TABLE... и все значения входных параметров SP.
Только корневой узел нуждается в пространстве имен
@VipinJayanarayanan, я дал ответ, следуя минимальному воспроизводимому шаблону примера. Вы копируете его в SSMS как есть, запускаете, и он работает.
@YitzhakKhabinsky, новый код работает нормально. Большое спасибо. Очень признателен.
Как уже упоминалось, вам нужно указать пространство имен в файле 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, я попытался запустить эту хранимую процедуру, которой вы поделились с пространством имен по умолчанию. Он не вставлял никаких записей.
Только что понял, что пространство имен нужно только корневому узлу, см. редактирование. Также смотрите скрипку dbfiddle.uk/EVOLWrSx
Привет, Ицхак, я пробовал это, но получаю ту же ошибку: Msg 2229, уровень 16, состояние 1, процедура InsertEDIInboundInvoiceNew, строка 150 [строка запуска партии 8] XQuery [nodes()]: имя «ns0» не обозначает пространство имен.