Вставка и обновление нескольких записей XML в SQL 2008 — решение, но, возможно, не правильное?

Итак, у меня есть следующие два тестовых/примерных запроса для INSERT и UPDATE данных из XML в таблицу SQL 2008. Довольно простой материал, заключающийся в том, что входящая структура XML будет сопоставлена ​​со структурой таблицы SQL (ну, обновление, конечно, имеет только необходимые поля - опять же просто пример).

ВСТАВИТЬ ЗАЯВЛЕНИЕ

declare @passedXML xml
set @passedXML='<root><record><name>Sam</name><age>37</age><comments /></record><record><name>Dan</name><age>32</age><comments /></record></root>'

insert into test (name, age, comments)
select  x.record.query('name').value('.', 'varchar(255)'),
        x.record.query('age').value('.', 'int'),
        x.record.query('comments').value('.','varchar(255)')
from @passedXML.nodes('root/record') as x(record)

Итак, проблем нет, две XML-записи вставляются по мере необходимости. Теперь переходим к оператору UPDATE:

ЗАЯВЛЕНИЕ ОБ ОБНОВЛЕНИИ

declare @passedXML xml
set @passedXML='<root><record><id>3</id><comments>This is a new comment</comments></record><record><id>2</id><comments>Michael Michael</comments></record></root>'
update test
set comments = (select  x.record.query('comments').value('.','varchar(255)')
    from @passedXML.nodes('root/record') as x(record)
    where id = x.record.query('id').value('.','bigint'))

Итак, обновление работает — поле комментариев двух XML-записей обновляется на основе переданного

МОЙ ВОПРОС

Конечно, учитывая приведенный выше пример UPDATE, предложение where взято из XML, а не из таблицы TEST.. поэтому, когда UPDATE выполняется, запрос говорит, что 100 (если сказать, что это количество записей в таблице TEST) обновлено. конечно, были обновлены только две (в данном случае) записи, но SQL должен был пройти через всю таблицу?? Я думаю, есть ли способ каким-то образом прикрепить предложение where из XML к базовой таблице TEST, если это имеет смысл? то есть ограничить TEST только обновленными элементами?


xml sql sql-server sql-server-2008 sqlxml
person Dav.id    schedule 28.06.2011    source источник
comment
ваш язык программирования С#?   -  person Pankaj    schedule 28.06.2011

Ответы (1)


arrow_upward
2
arrow_downward

Позвольте мне немного изменить ваш запрос на обновление:

  declare @passedXML xml
set @passedXML='<root><record><name>Sam</name><age>37</age><comments /></record><record><name>Dan</name><age>32</age><comments /></record></root>'

DECLARe @test TABLE (id bigint IDENTITY(1,1),name nvarchar(100), age int, comments varchar(255))
insert into @test (name, age, comments)
select  x.record.query('name').value('.', 'varchar(255)'),
        x.record.query('age').value('.', 'int'),
        x.record.query('comments').value('.','varchar(255)')
from @passedXML.nodes('root/record') as x(record)


set @passedXML='<root><record><id>99999</id><comments>This is a new comment</comments></record><record><id>2</id><comments>Michael Michael</comments></record></root>'
; with CTE as( 
SELECT  x.record.query('comments').value('.','varchar(255)') comment,
        x.record.query('id').value('.','int') id
    from @passedXML.nodes('root/record') as x(record))
update @test
set comments = cte.comment
FROM @test T
JOIN CTE oN cte.id=t.id

Результат обновления: (1 row(s) affected)

SQL-сервер достаточно умен, чтобы не обновлять каждую строку. Если вы посмотрите на план выполнения, вы увидите, что хеш-совпадение имеет только одно фактическое количество строк, а не два:

введите здесь описание изображения

@@RoWCOUNT тоже покажет вам 1, и это нормально.

В вашем примере вы уже обновляете каждую строку (если в XML нет такого идентификатора, в комментариях должен быть вставлен NULL)

person Dalex    schedule 28.06.2011
comment
Это очень круто.. Должен признаться, я видел утверждения CTE, но никогда не знал, для чего они нужны… еще одно приключение! Так что спасибо, прекрасный пример, и спасибо за то, что включили статистику - конечно, имеет больше смысла обновлять только требуемую строку!! - person Dav.id; 28.06.2011