October 26, 2009 at 1:38 am
Hello,
I want to update a table from xml that is supplied.
My XML
<items><item id="1" ISDELETED="D"></item><item id="2" ISDELETED="D"></item><item id="3" ISDELETED="D"></item></items>
I need to update isdeleted field depending on the ID. Table name TBL_NEWS
please provide me the stored proc
October 26, 2009 at 10:09 am
You can use the noeds and value XML methods together in order to get the information in tabular format. If you’ll do it in a CTE, you’ll get the results and you’ll be able to use it as if it was a table. Here is the part that creates the CTE. From here it is just a regular update statement that uses 2 tables.
declare @xml xml
set @xml = '<items><item id="1" ISDELETED="D"></item><item id="2" ISDELETED="D"></item><item id="3" ISDELETED="D"></item></items>'
;with MyCTE as (
select xmlcol.value('@id','int') as ID,
xmlcol.value ('@ISDELETED', 'char(1)') as IsDeleted
from @xml.nodes ('/items/item') tbl (xmlcol))
select * from MyCTE
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 29, 2009 at 11:38 pm
declare @xml xml
set @xml = '<items><item id="1" ISDELETED="D"></item><item id="2" ISDELETED="D"></item><item id="3" ISDELETED="D"></item></items>'
Update MyTable
Set ISDELETED = B.ISDELETED
From MyTable A
Inner Join
(
select xmlcol.value('@id','int') as ID,
xmlcol.value ('@ISDELETED', 'char(1)') as IsDeleted
from @xml.nodes ('/items/item') tbl (xmlcol)
) B on B.ID = A.ITEMID
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply