substituir o valor de (XML DML)

Artigo
06/18/2013

Atualiza o valor de um nó no documento.

Sintaxe

replace value of 
      Expression1 
with
      Expression2

Argumentos

Expression1
Identifica um nó cujo valor será atualizado. Deve identificar apenas um único nó. Ou seja, Expression1 deve ser um singleton estático. Se o XML for digitado, o tipo do nó deverá ser um tipo simples. Se forem selecionados vários nós, um erro será gerado. Se Expression1 retornar uma sequência vazia, nenhuma substituição de valor ocorrerá e nenhum erro será retornado. Expression1 deve retornar um único elemento que tem conteúdo de tipo simples (lista ou tipos atômicos), um nó de texto ou um nó de atributo. Expression1 não pode ter um tipo de união, um tipo complexo, uma instrução de processamento, um nó de documento ou um nó de comentários. Se for, um erro será retornado.
Expression2
Identifica o novo valor do nó. Pode ser uma expressão que retorna um nó digitado simples, porque data() será usado implicitamente. Se o valor for uma lista de valores, a instrução update substituirá o antigo valor pela lista. Ao modificar uma instância XML digitada, Expression2 deverá ser do mesmo tipo ou um subtipo de Expression1. Caso contrário, um erro é retornado. Ao modificar uma instância XML não digitada, Expression2 deverá ser uma expressão que pode ser atomizada. Caso contrário, um erro é retornado.

Exemplos

Os exemplos a seguir da instrução replace value of XML DML ilustra como atualizar nós em um documento XML.

A.Substituindo 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. Depois, as instruções replace value of 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

Observe que 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.Usando a expressão if para determinar o valor de substituição

É possível especificar a expressão if em Expression2 da instrução replace value of XML DML , como mostra o exemplo a seguir. Expression1 identifica que o atributo LaborHours do primeiro centro de trabalho será atualizado. Expression2 usa uma expressão if para determinar o novo valor do atributo LaborHours.

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.Atualizando XML armazenado em uma coluna XML sem tipo

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.Atualizando XML armazenado em uma coluna XML com tipo

Este exemplo substitui os valores em um documento de instruções de fabricação armazenado em uma coluna XML digitada.

No exemplo, primeiro você cria uma tabela (T) com uma coluna XML digitada no banco de dados AdventureWorks. Em seguida, você copia a instância XML de instruções de fabricação da coluna Instructions na tabela ProductModel para a tabela T. Em seguida, as inserções são aplicadas no XML da tabela T.

use AdventureWorks
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="https://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="https://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="https://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

Observe o uso de cast ao substituir o valor LotSize. Isso é necessário quando o valor dever ser de um tipo específico. Neste exemplo, se 500 fosse o valor, a conversão explícita não seria necessária.