SQL Server Update XML node from XML document in XML Column

unftdfkk 于 2023-06-28 发布在 SQL Server

关注(0)|答案(1)|浏览(147)

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

The field CAMPOS_ADICIONALES has the following XML data stored:

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

I need to update the value of the <CA_TAG> 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?

sql-server

来源：https://stackoverflow.com/questions/76544440/sql-server-update-xml-node-from-xml-document-in-xml-column

1条答案

按热度按时间

ldioqlga1#

Please try the following solution.

It shows how to update a typed XML column.

Note the use of cast as ... when replacing the <CA_TAG> 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'<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

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'<CAMPOS_ADICIONALES>
    <CA_TAG></CA_TAG>
</CAMPOS_ADICIONALES>');

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

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server Update XML node from XML document in XML Column

1条答案

相关问题

热门标签

最新问答