substituir o valor de (XML DML)
Aplica-se a: SQL Server
Banco de Dados SQL do Azure
Instância Gerenciada de SQL do Azure
Atualiza o valor de um nó no documento.
Sintaxe
replace value of Expression1
with Expression2
Observação
Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.
Argumentos
Expression1
Identifica um nó cujo valor será atualizado. Deve identificar apenas um único nó. Ou seja, a Expression1 precisa ser um singleton estático. Se o XML for digitado, o tipo do nó deverá ser um tipo simples. Quando forem selecionados vários nós, um erro será gerado. Se Expression1 retornar uma sequência vazia, não ocorrerá nenhuma substituição de valor e nenhum erro será retornado. Expression1 precisa retornar um único elemento que tenha um conteúdo de tipo simples (tipos de lista ou atômicos), um nó de texto ou um nó de atributo. Expression1 não pode ser um tipo de união, um tipo complexo, uma instrução de processamento, um nó de documento nem um nó de comentário ou um erro será retornado.
Expression2
Identifica o novo valor do nó. Pode ser uma expressão que retorna um nó de tipo simples, porque data()
é usada implicitamente. Se o valor for uma lista de valores, a update
instrução substituirá o valor antigo pela lista. Quando você modifica uma instância XML tipada, Expression2 deve ser do mesmo tipo ou um subtipo de Expression1. Caso contrário, um erro é retornado. Quando você modifica uma instância XML não tipada, Expression2 deve ser uma expressão que pode ser atomizada. Caso contrário, um erro é retornado.
Exemplos
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022
ou AdventureWorksDW2022
, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
Os exemplos a seguir da replace value of
instrução XML DML ilustram como atualizar nós em um documento XML.
R. Substituir valores em uma instância XML
No exemplo a seguir, uma instância de documento é atribuída primeiro a uma variável do tipo XML. Em seguida, replace value of
as instruções XML DML atualizam os valores no documento.
DECLARE @myDoc XML;
SET @myDoc = '<Root>
<Location LocationID="10"
LaborHours="1.1"
MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>';
SELECT @myDoc;
-- update text in the first manufacturing step
SET @myDoc.modify('
replace value of (/Root/Location/step[1]/text())[1]
with "new text describing the manu step"
');
SELECT @myDoc;
-- update attribute value
SET @myDoc.modify('
replace value of (/Root/Location/@LaborHours)[1]
with "100.0"
');
SELECT @myDoc;
O destino atualizado deve ser, no máximo, um nó que seja explicitamente especificado na expressão de caminho adicionando um "[1]" no fim da expressão.
B. Use a expressão if para determinar o valor de substituição
Você pode especificar a if
expressão em Expression2 da replace value of
instrução, conforme mostrado no exemplo a seguir. Expressão1 identifica o LaborHours
atributo do primeiro centro de trabalho a ser atualizado. Expression2 usa uma if
expressão para determinar o novo valor do LaborHours
atributo.
DECLARE @myDoc XML;
SET @myDoc = '<Root>
<Location LocationID="10"
LaborHours=".1"
MachineHours=".2" >Manu steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>';
--SELECT @myDoc
SET @myDoc.modify('
replace value of (/Root/Location[1]/@LaborHours)[1]
with (
if (count(/Root/Location[1]/step) > 3) then
"3.0"
else
"1.0"
)
');
SELECT @myDoc;
C. Atualizar XML armazenado em uma coluna XML não tipada
O exemplo a seguir atualiza XML armazenado em uma coluna:
DROP TABLE T;
GO
CREATE TABLE T (i INT, x XML);
GO
INSERT INTO T
VALUES (
1,
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
);
GO
-- verify the current <ProductDescription> element
SELECT x.query(' /Root/ProductDescription')
FROM T;
-- update the ProductName attribute value
UPDATE T
SET x.modify('
replace value of (/Root/ProductDescription/@ProductName)[1]
with "New Road Bike" ');
-- verify the update
SELECT x.query(' /Root/ProductDescription');
FROM T
D. Atualizar XML armazenado em uma coluna XML tipada
Este exemplo substitui os valores em um documento de instruções de fabricação armazenado em uma coluna XML digitada.
No exemplo, você primeiro cria uma tabela (T
) com uma coluna XML digitada AdventureWorks2022
no banco de dados. Em seguida, copie uma instância XML de instruções de fabricação da Instructions
coluna da tabela para a ProductModel
tabela T
. As inserções são então aplicadas ao XML na tabela T
.
USE AdventureWorks2022;
GO
DROP TABLE T;
GO
CREATE TABLE T (
ProductModelID INT PRIMARY KEY,
Instructions XML(Production.ManuInstructionsSchemaCollection)
);
GO
INSERT T
SELECT ProductModelID, Instructions
FROM Production.ProductModel
WHERE ProductModelID = 7;
GO
--insert a new location - <Location 1000/>.
UPDATE T
SET Instructions.modify('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
insert <MI:Location LocationID="1000" LaborHours="1000" LotSize="1000" >
<MI:step>Do something using <MI:tool>hammer</MI:tool></MI:step>
</MI:Location>
as first
into (/MI:root)[1]
');
GO
SELECT Instructions
FROM T;
GO
-- Now replace manu. tool in location 1000
UPDATE T
SET Instructions.modify('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
replace value of (/MI:root/MI:Location/MI:step/MI:tool)[1]
with "screwdriver"
');
GO
SELECT Instructions
FROM T;
-- Now replace value of lot size
UPDATE T
SET Instructions.modify('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
replace value of (/MI:root/MI:Location/@LotSize)[1]
with 500 cast as xs:decimal ?
');
GO
SELECT Instructions
FROM T;
Este exemplo usa cast
ao substituir LotSize
valor. cast
é necessário quando o valor deve ser de um tipo específico. Neste exemplo, if 500
é o valor, a conversão explícita não é necessária.