使用 XQuery 命名空间循环和插入 XML 数据并将其插入 MS SQL Server 表时出错-解网

问：

描述：我目前正在处理一项任务，该任务涉及解析 XML 文档并将其数据插入 SQL Server 表中。XML 文档包含发票信息，我需要遍历每张发票并将数据插入到两个表中：EDIInboundInvoiceHeader 和 EDIInboundInvoiceLineItems。但是，我在尝试插入数据时遇到了 XQuery 命名空间声明的问题。问题：执行存储过程以将 XML 数据插入到表中时，遇到以下错误：

消息 2229，级别 16，状态 1，过程插入EDIInboundInvoiceNew，第 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

sql-server 存储过程 xml 解析命名空间 xquery

答：

0赞 Yitzhak Khabinsky 8/10/2023 #1

您需要先声明命名空间，然后才能使用它们查询 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

InvoiceHeaderID	发票 ID	业务部	发票编号
1	INV0001	BU123型	INV123456
2	INV0002	BU456型	INV789012

EDIInboundInvoiceLineItems

InvoiceHeaderID	行号	线型	量	发票数量
1	1	项目	500.00	10
1	2	项目	800.00	5
1	3	项目	300.00	3
2	1	项目	800.00	8

0赞 Vipin Jayanarayanan 8/10/2023

嗨，Yitzhak，我试过了，但我收到同样的错误：消息 2229，级别 16，状态 1，过程 InsertEDIInboundInvoiceNew，第 150 行 [批处理开始第 8 行] XQuery [nodes（）]：名称“ns0”不表示命名空间。

0赞 Yitzhak Khabinsky 8/10/2023

有两个级别：InvoiceHeader 和 InvoiceLineItems。因此，您需要应用我在两个级别上展示的修复程序。

0赞 Vipin Jayanarayanan 8/10/2023

嗨，伊扎克，我在两个级别都进行了更改： WITH XMLNAMESPACES（'Invoice.Common.Schemas.InvoiceCanonical' AS ns0） FROM @InvoiceXML.nodes（'/ns0：InvoiceCanonical[sql：variable（“@CurrentInvoice”）]/ns0：Header'） AS InvoiceHeader（InvoiceHeader） FROM @InvoiceXML.nodes（'/ns0：InvoiceCanonical[sql：variable（“@CurrentInvoice”）]/ns0：Detail'） AS InvoiceItem（InvoiceItem）

0赞 Yitzhak Khabinsky 8/10/2023

请将 T-SQL 及其修复程序添加到原始问题中。

0赞 Vipin Jayanarayanan 8/10/2023

嗨，@YitzhakKhabinsky，我已经在原始问题中添加了修改后的代码

0赞 Charlieface 8/10/2023 #2

如前所述，您需要在 .但只有第一个节点需要指定它，其他节点都在空命名空间中。WITH XMLNAMESPACESInvoiceCanonical

您可以进一步大规模改进此代码，而不是使用光标，然后重新关联到另一个查询中。OUTPUT inserted.InvoiceHeaderID INTO @tableVariableINSERT
您也不需要单独的，因为您可以执行另一个操作来将所有内容选择回客户端。SELECTOUTPUT
不要使用那个可怕的，而只是让错误正确地冒回客户端。TRY CATCH
用于在 XQuery 中获得更好的性能。/text()

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)');

db<>fiddle

0赞 Vipin Jayanarayanan 8/10/2023

嗨，@Charlieface，我尝试运行您与默认命名空间共享的此存储过程。它没有插入任何记录。

0赞 Charlieface 8/11/2023

刚刚意识到只有根节点需要命名空间，请参阅编辑。另请参阅小提琴 dbfiddle.uk/EVOLWrSx

上一个：Jupyter Notebooks 中的变量未重置是否有问题？

下一个：如何在R模块之间相互传输无功值？

使用 XQuery 命名空间循环和插入 XML 数据并将其插入 MS SQL Server 表时出错

Error when Looping and Inserting XML Data into MS SQL Server Tables with XQuery Namespace

评论

评论

评论