SQL Server XML列节点更新：类型错误与动态XPath问题及解决

环境与数据结构 #

1. 表结构 #

CREATE TABLE [dbo].[STA11]
(   
    [ID_STA11] [dbo].[int] NOT NULL,    
    [COD_ARTICU] [varchar](15) NOT NULL,        
    [CAMPOS_ADICIONALES] [xml](CONTENT [dbo].[CAMPOS_ADICIONALES_STA11]) NULL
    
 CONSTRAINT [PK_STA11] PRIMARY KEY CLUSTERED 
(
    [ID_STA11] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2. XML Schema集合 #

CREATE XML SCHEMA COLLECTION [dbo].[CAMPOS_ADICIONALES_STA11] AS 
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="CAMPOS_ADICIONALES">
        <xsd:complexType>
            <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                    <xsd:all>
                        <xsd:element name="CA_TAG" type="CA_TAG_schemeType" minOccurs="0" nillable="true"/>
                    </xsd:all>
                </xsd:restriction>
            </xsd:complexContent>
        </xsd:complexType>
    </xsd:element>
    <xsd:simpleType name="CA_TAG_schemeType">
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="999"/>
        </xsd:restriction>
    </xsd:simpleType>
</xsd:schema>'
GO

3. 存储的XML数据 #

<CAMPOS_ADICIONALES>
  <CA_TAG></CA_TAG>
</CAMPOS_ADICIONALES>

首次更新尝试与错误 #

尝试使用modify()方法更新<CA_TAG>节点值：

UPDATE STA11
SET CAMPOS_ADICIONALES.modify('
  replace value of (/CAMPOS_ADICIONALES/CA_TAG/text())[1]
  with sql:variable("@ValorCampoAdicional")
')
WHERE COD_ARTICU = '0100100129';

触发错误：

Error: Msg 9312, Level 16, State 1, Line 6

XQuery [STA11.CAMPOS_ADICIONALES.modify()]: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found 'element(CA_TAG,CA_TAG_schemeType) *'.

另一种写法同样报错：

UPDATE [STA11]
SET CAMPOS_ADICIONALES.modify('
    replace value of (/CAMPOS_ADICIONALES/CA_TAG)[1]/text()[1]
    with "basura"
')
WHERE [COD_ARTICU] = '0100100129'

错误信息：

Error: Msg 9312, Level 16, State 1, Line 31
XQuery [STA11.CAMPOS_ADICIONALES.modify()]: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found 'element(CA_TAG,CA_TAG_schemeType) ?'.

固定节点的正确更新方案 #

通过类型转换实现固定节点的更新：

DECLARE @ValorCampoAdicional VARCHAR(100) = 'Hello there';
UPDATE dbo.STA11
    SET CAMPOS_ADICIONALES.modify('
      replace value of (/CAMPOS_ADICIONALES/CA_TAG)[1]
      with sql:variable("@ValorCampoAdicional") cast as CA_TAG_schemeType?
    ')
    WHERE COD_ARTICU = '0100100129';

动态指定节点名的问题 #

尝试动态指定要更新的节点名时，两种方式均失败：

方式一：字符串拼接XPath #

DECLARE @ValorCampoAdicional VARCHAR(100) = 'Hello there';
DECLARE @CampoAdicional VARCHAR(100) = 'CA_TAG';

UPDATE dbo.STA11
SET CAMPOS_ADICIONALES.modify('
  replace value of (/CAMPOS_ADICIONALES/' + @CampoAdicional + ')[1]
  with sql:variable("@ValorCampoAdicional") cast as CA_TAG_schemeType?
')
WHERE COD_ARTICU = '0100100129';

错误：

Msg 8172, Level 16, State 1, Line 5
The argument 1 of the XML data type method "modify" must be a string literal.

方式二：使用sql:variable()传入节点名 #

DECLARE @ValorCampoAdicional VARCHAR(100) = 'Hello there';
DECLARE @CampoAdicional VARCHAR(100) = 'CA_TAG';

UPDATE dbo.STA11
SET CAMPOS_ADICIONALES.modify('
  replace value of (/CAMPOS_ADICIONALES/sql:variable("@CampoAdicional"))[1]
  with sql:variable("@ValorCampoAdicional") cast as CA_TAG_schemeType?
')
WHERE COD_ARTICU = '0100100129';

错误：

Msg 9335, Level 16, State 1, Line 6
XQuery [dbo.STA11.CAMPOS_ADICIONALES.modify()]: The XQuery syntax '/function()' is not supported.

最终解决方案 #

创建用户定义函数fn_SetearCampoAdicional，通过字符串替换结合XML查询实现动态节点更新：

IF (OBJECT_ID('fn_SetearCampoAdicional','FN') IS NOT NULL) DROP FUNCTION fn_SetearCampoAdicional
GO
CREATE FUNCTION fn_SetearCampoAdicional(
    @dataXML XML,
    @CampoAdicional VARCHAR(999),
    @ValorCampoAdicional VARCHAR(999)
)
RETURNS XML
AS
BEGIN    

    DECLARE @tagApertura varchar(999)
    DECLARE @tagCierre varchar(999)
    DECLARE @xmlInsert AS XML 
    DECLARE @resultado AS XML

    set @tagApertura = '<' + @CAMPOADICIONAL+ '>'
    set @tagCierre = '</' + @CAMPOADICIONAL+ '>'

    set @xmlInsert = @tagApertura + @ValorCampoAdicional + @tagCierre

    set @resultado = (select CAST(REPLACE(CAST(@dataXML AS NVARCHAR(MAX))
                               ,CAST(@dataXML.query('/CAMPOS_ADICIONALES/*[local-name(.) eq sql:variable("@CampoAdicional")][1]') AS NVARCHAR(MAX))
                               ,CAST(@xmlInsert AS NVARCHAR(MAX)))AS XML))

    RETURN @resultado
END;
GO

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