XML根节点带命名空间时SQL的modify方法失效求助

数据老陈

2026-8-5

解决带命名空间的XML在SQL Server中modify语句失效问题

我已实现XML修改功能，但当XML根节点<SyncReceiveDelivery>带有xmlns="http://schema.infor.com/InforOAGIS/2"和releaseID="9.2"属性时，SQL中的SET @myDoc.modify语句无法正常执行；移除这些属性则功能正常。由于该XML来自供应商，无法删除属性，需修改modify语句使其适配带命名空间的XML。

原始SQL代码

DECLARE @myDoc xml
SET @myDoc =
'<?xml version="1.0" encoding="UTF-8"?>
<SyncReceiveDelivery xmlns="http://schema.infor.com/InforOAGIS/2" releaseID="9.2">
  <ApplicationArea>
    <Sender>
      <LogicalID>infor.azure.azure_prd</LogicalID>
      <ComponentID>External</ComponentID>
      <ConfirmationCode>OnError</ConfirmationCode>
    </Sender>
    <CreationDateTime>2022-12-30T08:44:36.267</CreationDateTime>
    <BODID>infor.azure_application_to_AIM_ReceiveDelivery:xyz1234:2022-12-30T08:44:36.267</BODID>
  </ApplicationArea>
  <DataArea>
    <Sync>
      <TenantID>AIM_PRD</TenantID>
      <AccountingEntityID>5140</AccountingEntityID>
      <Location>5140</Location>
      <ActionCriteria>
        <ActionExpression actionCode="Add"/>
      </ActionCriteria>
    </Sync>
    <ReceiveDelivery>
      <ReceiveDeliveryHeader>
        <DocumentID>
          <ID>xyz1234</ID>
        </DocumentID>
        <DocumentDateTime>2022-12-28T11:46:13.7300000</DocumentDateTime>
        <Description>3P Manifest - xyz1234</Description>
        <Status>
          <Code>Pending</Code>
          <Description>Pending</Description>
          <EffectiveDateTime>2022-12-30T14:44:36.3343520Z</EffectiveDateTime>
        </Status>
        <PackingSlip>xyz1234</PackingSlip>
      </ReceiveDeliveryHeader>
      <ReceiveDeliveryItem>
        <Classification>
          <Codes>
            <Code listID="Classes" sequence="1">*</Code>
          </Codes>
        </Classification>
        <ServiceIndicator/>
        <PurchaseOrderReference>
          <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">14500000999</ID>
          </DocumentID>
          <LineNumber>1</LineNumber>
        </PurchaseOrderReference>
        <ReceivedQuantity unitCode="EA">44</ReceivedQuantity>
        <LineNumber>63</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
          </Property>
        </UserArea>
      </ReceiveDeliveryItem>
      <ReceiveDeliveryItem>
        <Classification>
          <Codes>
            <Code listID="Classes" sequence="1">*</Code>
          </Codes>
        </Classification>
        <ServiceIndicator/>
        <PurchaseOrderReference>
          <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">14500000999</ID>
          </DocumentID>
          <LineNumber>1</LineNumber>
        </PurchaseOrderReference>
        <ReceivedQuantity unitCode="EA">100</ReceivedQuantity>
        <LineNumber>51</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
          </Property>
        </UserArea>
      </ReceiveDeliveryItem>
      <ReceiveDeliveryItem>
        <Classification>
          <Codes>
            <Code listID="Classes" sequence="1">*</Code>
          </Codes>
        </Classification>
        <ServiceIndicator/>
        <PurchaseOrderReference>
          <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">14500000999</ID>
          </DocumentID>
          <LineNumber>1</LineNumber>
        </PurchaseOrderReference>
        <ReceivedQuantity unitCode="EA">100</ReceivedQuantity>
        <LineNumber>75</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
          </Property>
        </UserArea>
      </ReceiveDeliveryItem>
      <ReceiveDeliveryItem>
        <Classification>
          <Codes>
            <Code listID="Classes" sequence="1">*</Code>
          </Codes>
        </Classification>
        <ServiceIndicator/>
        <PurchaseOrderReference>
          <DocumentID>
            <ID accountingEntity="9991" lid="lid://infor.eam.aim_prd" location="9991">15000000999</ID>
          </DocumentID>
          <LineNumber>1</LineNumber>
        </PurchaseOrderReference>
        <ReceivedQuantity unitCode="EA">100</ReceivedQuantity>
        <LineNumber>75</LineNumber>
        <UserArea>
          <Property>
            <NameValue name="UDFCHAR02">S00603851</NameValue>
          </Property>
          <Property>
            <NameValue name="UDFCHAR03">1</NameValue>
          </Property>
        </UserArea>
      </ReceiveDeliveryItem>
    </ReceiveDelivery>
  </DataArea>
</SyncReceiveDelivery>'

declare @po nvarchar(50)
declare @poline nvarchar(10)
declare @reqline nvarchar(10)

set @po = '14500000999'
set @poline = '1'
set @reqline = '63'

select  @myDoc 

SET @myDoc.modify('replace value of 
(/SyncReceiveDelivery/DataArea/ReceiveDelivery/ReceiveDeliveryItem[LineNumber[text()=sql:variable("@reqline")]]
    [PurchaseOrderReference/DocumentID/ID[text()=sql:variable("@po")]]
    [PurchaseOrderReference/LineNumber[text()=sql:variable("@poline")]]/ReceivedQuantity/text())[1]
with "99"')

解决方案

问题核心是XML命名空间未被显式引用，SQL Server的XQuery需要绑定命名空间前缀才能定位到带命名空间的节点。修改后的代码如下：

-- 声明XML命名空间，绑定前缀到目标URI
WITH XMLNAMESPACES (DEFAULT 'http://schema.infor.com/InforOAGIS/2')
DECLARE @myDoc xml
SET @myDoc =
'<?xml version="1.0" encoding="UTF-8"?>
<SyncReceiveDelivery xmlns="http://schema.infor.com/InforOAGIS/2" releaseID="9.2">
  <!-- 省略XML内容，与原始代码一致 -->
</SyncReceiveDelivery>'

declare @po nvarchar(50)
declare @poline nvarchar(10)
declare @reqline nvarchar(10)

set @po = '14500000999'
set @poline = '1'
set @reqline = '63'

select  @myDoc 

SET @myDoc.modify('replace value of 
(/SyncReceiveDelivery/DataArea/ReceiveDelivery/ReceiveDeliveryItem[LineNumber[text()=sql:variable("@reqline")]]
    [PurchaseOrderReference/DocumentID/ID[text()=sql:variable("@po")]]
    [PurchaseOrderReference/LineNumber[text()=sql:variable("@poline")]]/ReceivedQuantity/text())[1]
with "99"')

或者也可以指定自定义前缀（比如oagis）来访问节点：

WITH XMLNAMESPACES ('http://schema.infor.com/InforOAGIS/2' AS oagis)
-- 后续modify语句中的路径需添加前缀
SET @myDoc.modify('replace value of 
(/oagis:SyncReceiveDelivery/oagis:DataArea/oagis:ReceiveDelivery/oagis:ReceiveDeliveryItem[oagis:LineNumber[text()=sql:variable("@reqline")]]
    [oagis:PurchaseOrderReference/oagis:DocumentID/oagis:ID[text()=sql:variable("@po")]]
    [oagis:PurchaseOrderReference/oagis:LineNumber[text()=sql:variable("@poline")]]/oagis:ReceivedQuantity/text())[1]
with "99"')

说明

使用WITH XMLNAMESPACES语句绑定XML中的命名空间，DEFAULT关键字会将该命名空间设为默认，无需在路径中添加前缀；也可以指定自定义前缀，需在所有节点前添加对应前缀
原有的sql:variable变量引用逻辑保持不变，不影响参数传递

内容的提问来源于stack exchange，提问作者LJHHouston

XML根节点带命名空间时SQL的modify方法失效求助

原始SQL代码 #

解决方案 #

说明 #

原始SQL代码

解决方案

说明