Início / coding / Perguntas / 79371751
Accepted
Leah
Asked: 2025-01-20 23:10:01 +0800 CST

Dados XML do SQL Server - Como inserir se estiver vazio ou atualizar se não estiver

  • 772

Tenho uma XMLvariável contendo uma tabela. Esta tabela tem um número de colunas e uma linha. Nas duas primeiras colunas, preciso atualizar seus valores com o resultado de um procedimento armazenado - inserindo um valor se estiver vazio e atualizando o valor se ele já existir.

Isto é o que eu tenho até agora. Por favor, note que os nomes/estrutura dos elementos na tabela são verdadeiros para como eles serão na realidade (ou seja, todas as linhas são chamadas de "Linha" e todas as colunas são chamadas de "Coluna"). Isto é devido a uma restrição do programa que produz a tabela XML.

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
    <Column>some data</Column>
    <Column>some more data</Column>
  </Row>
</Table>'

-- View xml table prior to modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

-- Declare new id and date variables. This will come from a stored procedure. I've made it static to simplify the example.
DECLARE @id int = 24
DECLARE @date datetime2(2) = sysutcdatetime()

-- If id is empty, insert the id value. Otherwise, update the id value.
IF (SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@id")} into (//Table/Row/Column)[1]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[1] with sql:variable("@id")')

-- If date is empty, insert the date value. Otherwise, update the date value
IF (SELECT tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@date")} into (//Table/Row/Column)[2]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[2] with sql:variable("@date")')

-- View xml table after modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

Isso parece funcionar; no entanto, estou pensando se há uma maneira de escrever a condição (ou seja, se estiver vazio, inserir; senão, substituir valor) dentro da própria função modify? Em outras palavras, posso evitar as IF...ELSEinstruções SQL e usar uma SETinstrução em vez disso?

Além disso, existe uma maneira de atualizar as duas colunas na mesma função de modificação ou isso precisa ser feito separadamente?

Desde já, obrigado!

EDIT Código de exemplo alterado para demonstrar que a tabela pode ter colunas adicionais e para esclarecer o formato da tabela XML.

sql-server

1 respostas

  1. Best Answer

    Tente a seguinte solução aproveitando o SQL Server XQuery e sua expressão FLWOR.

    Para este método, não importa se os valores XML existem ou não.

    Meu entendimento é que seu exemplo XML é artificial, ou seja, os elementos XML são todos chamados Column . É por isso que no cenário da vida real você pode usar local-name()a função XPath para distinguir elementos XML por nomes em vez de suas posições.

    SQL #1

    -- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
  </Row>
</Table>';

-- View xml table prior to modifications
SELECT col.value('Column[1]/text()[1]', 'int') 'id'
      , col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
FROM @x.nodes('/Table/Row') tbl(col);

-- Declare new id and date variables.
DECLARE @id int = 24;
DECLARE @date datetime2(2) = sysutcdatetime();

SET @x = @x.query('<Table>
{
   for $y in /Table/Row
   return <Row>
   {
        for $z in $y/*
        return if ($z is $y/*[1]) then 
                element Column {sql:variable("@id")}
            else
            (
                element Column {sql:variable("@date")}
            )
    }
   </Row>
}
</Table>');

-- View xml table after modifications
SELECT col.value('Column[1]/text()[1]', 'int') 'id'
      , col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
FROM @x.nodes('/Table/Row') tbl(col);

    Saída

    eu ia data
    24 2025-01-20 15:39:12.74

    SQL #2

    -- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <ID></ID>
    <Date></Date>
    <State>FL</State>
    <City>Miami</City>
  </Row>
</Table>';

-- View xml table prior to modifications
SELECT col.value('(ID/text())[1]', 'int') AS [id]
      , col.value('(Date/text())[1]', 'datetime2(2)') AS [date]
      , col.value('(State/text())[1]', 'char(2)') AS [State]
      , col.value('(City/text())[1]', 'varchar(20)') AS [city]
FROM @x.nodes('/Table/Row') AS tbl(col);

-- Declare new id and date variables.
DECLARE @id int = 24;
DECLARE @date datetime2(2) = sysutcdatetime();

SET @x = @x.query('<Table><Row>
{
    for $z in /Table/Row/*
    return if (local-name($z)="ID") then 
            element ID {sql:variable("@id")}
        else if (local-name($z)="Date") then 
        (
            element Date {sql:variable("@date")}
        )
        else $z
}
</Row></Table>');

-- View xml table after modifications
SELECT  col.value('(ID/text())[1]', 'int') AS [id]
      , col.value('(Date/text())[1]', 'datetime2(2)') AS [date]
      , col.value('(State/text())[1]', 'char(2)') AS [State]
      , col.value('(City/text())[1]', 'varchar(20)') AS [city]
FROM @x.nodes('/Table/Row') AS tbl(col);

    Saída

    eu ia data Estado cidade
    24 2025-01-20 15:58:43.00 FL Miami
    • 2

relate perguntas