SQL Server Update XML node from XML document in XML Column

Question

I need some help: I'm trying to update an XML node from an XML document stored in an XML column from my database.

This is the structure of my table:

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

The field CAMPOS_ADICIONALES has an XML SCHEMA stored in the database, this XML schema has the following structure:

CREATE XML SCHEMA COLLECTION [dbo].[CAMPOS_ADICIONALES_STA11] AS 
N'
    
        
            
                
                    
                        
                    
                
            
        
    
    
        
            
        
    
'
GO

The field CAMPOS_ADICIONALES has the following XML data stored:

I need to update the value of the element in the XML document, but all of the approaches I was trying gave me an error. So far I tried:

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

resulting in:

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) *'.

Another approach:

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

and its result:

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) ?'.

And other similar attempts, but with the same result or a similar error, it seems to be a problem with the datatype of the element, but I can't find any way to cast a value within the XQuery syntax.

Is it possible to do this without the use of the REPLACE function in combination with other STRING functions of TSQL?
Is there a "native" XML method?

What do you think?

UPDATE: @YitzhakKhabinsky has pointed me in the right direction, the way to do this is using the following TSQL Code:

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

But here is a new problem...with this code we can update an XML field with the characteristics that I mentioned before, and we can use an SQL Variable to dynamically update the value, so the new problem is:

As you can see, the xml node that I'm trying to update is

/CAMPOS_ADICIONALES/CA_TAG

Is there a way to construct dynamically this XPath inside the statement or inside a function? If I try to do this:

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

I get the following error:

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

Completion time: 2023-06-29T15:26:09.7878630-03:00

Or if I try to do this:

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

I get the this error:

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

Completion time: 2023-06-29T15:27:23.5358094-03:00

So, this is the situation @YitzhakKhabinsky, I've tried to put this in a comment but was impossible, and a new Question will not contribute to enriching the community.

UPDATE 2 - Finally the solution

For those who have a similar situation, here's is the code that solves my problem. Its an UDF in SQL Server which return an XML object and use that result in an UPDATE statement for an XML field:

Thanks again to @YitzhakKhabinsky who pointed me in the right direction:

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

    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

Yitzhak Khabinsky · Accepted Answer

Please try the following solution.

It shows how to update a typed XML column.

Note the use of cast as ... when replacing the element value. It is required when the value must be of a specific user defined type as defined by the XML Schema.

If an element is one of W3C “http://www.w3.org/2001/XMLSchema” data types, its value can be specified directly without casting.

SQL

USE tempdb;
GO

CREATE XML SCHEMA COLLECTION dbo.CAMPOS_ADICIONALES_STA11 AS 
N'
    
        
            
                
                    
                        
                    
                
            
        
    
    
        
            
        
    
';
GO

CREATE TABLE dbo.STA11
(   
    ID_STA11 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

INSERT dbo.STA11 (ID_STA11, COD_ARTICU, CAMPOS_ADICIONALES) VALUES
(0, '0100100129',  
    N'
    
');

-- before
SELECT * FROM dbo.STA11
WHERE COD_ARTICU = '0100100129';

UPDATE dbo.STA11
SET CAMPOS_ADICIONALES.modify('
  replace value of (/CAMPOS_ADICIONALES/CA_TAG)[1]
  with "Hello" cast as CA_TAG_schemeType?
')
WHERE COD_ARTICU = '0100100129';

-- after
SELECT * FROM dbo.STA11
WHERE COD_ARTICU = '0100100129';

SQL #2

If you need to update by using a T-SQL variable.

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

SQL Server Update XML node from XML document in XML Column

Answers (1)

Related Questions