Update XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update XML

Author

Topic

shan_savi
Starting Member

6 Posts

Posted - 2008-09-22 : 11:33:43

Hi

Is there any update statement to update a table if i receive a XML as a parameter? We have an insert statement to insert a record into the table from a SP which as xml as a parameter. Like this:

INSERT into testingxml select x.value('Cust_Id[1]','varchar(200)'),

x.value('Cust_Name[1]','varchar(200)')

from @xmlDoc.nodes('/Customers/Customer') Customers(x)

exec test_xml_testing '<Customers><Customer><Cust_Id>TestCustId2</Cust_Id>

<Cust_Name>TestingCustName2</Cust_Name>

</Customer></Customers>'

But i'm looking for update statement

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 11:49:53

what is the primary key of your table?

shan_savi
Starting Member

6 Posts

Posted - 2008-09-22 : 11:58:02

quote:
Originally posted by visakh16

what is the primary key of your table?

Cust_No

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 12:00:15

But your xml does not seem to contain custno value. then how will you update the table with values of xml? how will you decide which values should be updated for which row?

shan_savi
Starting Member

6 Posts

Posted - 2008-09-22 : 12:01:24

quote:
Originally posted by visakh16

But your xml does not seem to contain custno value. then how will you update the table with values of xml? how will you decide which values should be updated for which row?

Sorry Cust_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 12:38:40

[code]UPDATE t
SET t.Cust_Name=q.Cust_Name,
...
FROM testingxml t
INNER JOIN (select x.value('Cust_Id[1]','varchar(200)'),

x.value('Cust_Name[1]','varchar(200)'),..

from @xmlDoc.nodes('/Customers/Customer') Customers(x))q
ON q.Cust_Id=t.Cust_Id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources