Update SQL2005 table with XML?

4 Oct 2007 3:50 PM

Moistly

I wish to update a bunch of records in a table with C#/ SQL2005,

Usually I would just create a bunch of SQL updates, but ideally would
like to do with 1 SQL Query.

I'm assuming using XML is a viable option

My XML looks like this

<TransQuestions>
    <TransQuestion>
      <ID>41080</ID>
      <QuestionNumber>1</QuestionNumber>
    </TransQuestion>
    <TransQuestion>
      <ID>38202</ID>
      <QuestionNumber>2</QuestionNumber>
    </TransQuestion>
</TransQuestions>

If I were to do it in my usual way I would have 2 sql statements

UPDATE TransQuestions SET QuestionNumber = 1 WHERE (QuestionID =
41080)
UPDATE TransQuestions SET QuestionNumber = 2 WHERE (QuestionID =
38202)

I am passing the above XML as a parameter

SqlParameter param = new SqlParameter("@xmlData", SqlDbType.Xml);
param.Value = sXML; // my built XML string

But after that I am a bit lost with how to construct my stored
procedure.

I would be grateful for any guidence/ pointers
Thanks,

4 Oct 2007 6:46 PM

Kent Tegels

Hello Moistly,

Here's how I'd do it:

use scratch
go
create table dbo.transQuestions(
    id int not null primary key
    , questionNumber int not null
)
go
set nocount on
insert into dbo.transQuestions values (41080,-1)
insert into dbo.transQuestions values (38202,-1)
go
create procedure dbo.updateTransQuestions(@x xml)
as begin
    ;with t(ID,questionNumber) as (
        select t.c.value('data(ID)[1]','int')
        , t.c.value('data(QuestionNumber)[1]','int')
        from @x.nodes('//TransQuestion') as t(c))
    update dbo.transQuestions
    set QuestionNumber = t.questionNumber
    from t
    where dbo.transQuestions.ID = t.id
end
go
exec dbo.updateTransQuestions '<TransQuestions><TransQuestion><ID>41080</ID><QuestionNumber>1</QuestionNumber></TransQuestion><TransQuestion><ID>38202</ID><QuestionNumber>2</QuestionNumber></TransQuestion></TransQuestions>'
go
select * from dbo.transquestions
go
drop procedure dbo.updateTransQuestions
drop table dbo.transQuestions
go

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

4 Oct 2007 6:48 PM

Joe Fawcett

Show quote

"Moistly" <pfranc***@gmail.com> wrote in message
news:1191513000.001032.268850@y42g2000hsy.googlegroups.com...
>I wish to update a bunch of records in a table with C#/ SQL2005,
>
> Usually I would just create a bunch of SQL updates, but ideally would
> like to do with 1 SQL Query.
>
> I'm assuming using XML is a viable option
>
> My XML looks like this
>
> <TransQuestions>
>    <TransQuestion>
>      <ID>41080</ID>
>      <QuestionNumber>1</QuestionNumber>
>    </TransQuestion>
>    <TransQuestion>
>      <ID>38202</ID>
>      <QuestionNumber>2</QuestionNumber>
>    </TransQuestion>
> </TransQuestions>
>
>
> If I were to do it in my usual way I would have 2 sql statements
>
> UPDATE TransQuestions SET QuestionNumber = 1 WHERE (QuestionID =
> 41080)
> UPDATE TransQuestions SET QuestionNumber = 2 WHERE (QuestionID =
> 38202)
>
> I am passing the above XML as a parameter
>
> SqlParameter param = new SqlParameter("@xmlData", SqlDbType.Xml);
> param.Value = sXML; // my built XML string
>
> But after that I am a bit lost with how to construct my stored
> procedure.
>
> I would be grateful for any guidence/ pointers
> Thanks,
>

This should get you started, it could be shortened to remove the table
variables but I think this makes it clearer what's going on:

USE TempDB;
DECLARE @XmlData XML;
SET @XmlData = '<TransQuestions>
<TransQuestion>
<ID>41080</ID>
<QuestionNumber>1</QuestionNumber>
</TransQuestion>
<TransQuestion>
<ID>38202</ID>
<QuestionNumber>2</QuestionNumber>
</TransQuestion>
</TransQuestions>';

DECLARE @Data TABLE
(
ID INT,
QuestionNumber INT
)

INSERT @Data
SELECT
c.value('ID[1]', 'INT'),
c.value('QuestionNumber[1]', 'INT')
FROM @XmlData.nodes('/*/TransQuestion') T(c);
SELECT * FROM @Data;

DECLARE @OriginalData TABLE
(
ID INT,
QuestionNumber INT
)
INSERT @OriginalData VALUES(41080, 3);
INSERT @OriginalData VALUES(38202, 4);

SELECT * FROM @OriginalData;

UPDATE @OriginalData
SET QuestionNumber = D.QuestionNumber
FROM @OriginalData O INNER JOIN @Data D
ON O.ID = D.ID;

SELECT * FROM @OriginalData;

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

5 Oct 2007 11:52 AM

Moistly

That's brilliant, thanks alot