CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008sqlxml
Dav.id
Dav.id

Reputation: 2797

Insert and Updating Multiple XML records to SQL 2008 - A solution, but maybe not the correct one?

Ok, so I have the following two test/example queries to INSERT and UPDATE data from XML into a SQL 2008 table. Fairly basic stuff in the fact the incoming XML structure will be matched to that of the SQL table (well the update of course has the necessary fields only - again just an example).

INSERT STATEMENT

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)

So, no problems there, the two XML "records" get inserted as we need to.. Now onto the UPDATE statement:

UPDATE STATEMENT

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

Ok, so the update works - two XML "records" have their comments field updated based on the passed

MY QUESTION IS

Of course given the sample UPDATE above, the where clause is from the XML and not the TEST table.. so when the UPDATE is executed, the query says 100 (if say that is how many records are in table TEST) records updated.. of course only the two (in this case) records have been updated, but SQL had to run through the whole table?? I guess is there a way to somehow have the where clause from the XML attached to the underlying TEST table if that makes sense? i.e. to limit TEST to only the items updated?

Upvotes: 1

Views: 5097

Answers (1)

Dalex
Dalex

Reputation: 3625

Let me change a little your update query:

  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

Update result: (1 row(s) affected)

SQL server is smart enough to do not update every row. If you look at execution plan, you can see that hash match has only one actual number of rows, not two:

enter image description here

@@RoWCOUNT will show to you 1 too and it is ok.

In your example you update every row already (if in XML there are no such id, NULL should be inserted in comments)

Upvotes: 2

Related Questions