OPENXML Question

[Robert]

How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @@ROWCOUNT function, but it always returns a 0.

Generic update example trying to return the # of rows updated:

declare @i int

exec sp_xml_preparedocument @i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'

update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID

RETURN @@ROWCOUNT --Returns a 0

exec sp_xml_removedocument @i

Thanks,

[Omnibuzz]

It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @@rowcount as see.

--

[sloan]

Check my procedure here:

It works for me (rowcount stuff that is)

if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO

CREATE PROCEDURE dbo.uspTitleUpdate (
@xml_doc TEXT ,
@numberRowsAffected int output --return
)

AS

SET NOCOUNT ON

DECLARE @hdoc INT -- handle to XML doc

DECLARE @errorTracker int -- used to "remember" the @@ERROR

DECLARE @updateRowCount int
DECLARE @insertRowCount int

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc

-- build a table (variable table) to store the xml-based result set
DECLARE @titleupdate TABLE (
identityid int IDENTITY (1,1) ,

title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,

--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0

)

--the next call will take the info IN the @hdoc(with is the holder for
@xml_doc), and put it IN a variableTable
INSERT @titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (

title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit

)

--select * from @titleupdate

--lets differeniate between existing (update) and new ones (insert)
Update @titleupdate
SET
alreadyExists = 1
FROM
@titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))

SET NOCOUNT OFF

Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0

Select @updateRowCount = @@ROWCOUNT

INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@titleupdate
WHERE
alreadyExists = 0

Select @insertRowCount = @@ROWCOUNT

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from titles

SET NOCOUNT OFF

GO

"Robert" <Rob...@discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E...@microsoft.com...