Deleting rows with OPENXML - 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 2000 Forums

Transact-SQL (2000)

Deleting rows with OPENXML

Author

Topic

wgraves
Starting Member

4 Posts

Posted - 2005-02-23 : 12:02:27

Hi there,

I'm trying to delete some rows from a table using an xml document (in memory) and OpenXML in SQL Server. I have the following code, but it will only delete the first record. Any ideas?

CREATE PROCEDURE [dbo].[CompanyBusinessUnit_DeleteCompanyBusinessUnit_Delete]
@CompanyHeirarchy nvarchar(4000)

AS

SET NOCOUNT ON;

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @CompanyHeirarchy

DELETE FROM CompanyBusinessUnit
WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
FROM OPENXML (@hdoc, '/NewDataSet/DeletedNodes',2) WITH (CompanyBusinessUnitID Integer))

EXEC sp_xml_removedocument @hDoc

GO

Thanks,

Wes

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-23 : 13:28:40

Can I be the first to say
XLM sucks

You can do anything at www.zombo.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-23 : 14:22:16

Official XML sucks thread

There is something wrong with the xml stuff(double meaning),
What resultset does this return ?
Select CompanyBusinessUnitID FROM OPENXML (@hdoc, '/NewDataSet/DeletedNodes',2) WITH (CompanyBusinessUnitID Integer)

rockmoose

wgraves
Starting Member

4 Posts

Posted - 2005-02-23 : 14:41:41

Hi there,

I got it to work with some help from a guy on the msdn newgroups. I had to change the select statement to:

(

Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3)

Thanks,

Wes

quote:
Originally posted by rockmoose

Official XML sucks thread

There is something wrong with the xml stuff(double meaning),
What resultset does this return ?
Select CompanyBusinessUnitID FROM OPENXML (@hdoc, '/NewDataSet/DeletedNodes',2) WITH (CompanyBusinessUnitID Integer)

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-23 : 14:49:44

Way to go Wes,
Temporarily forgot that XML is a weakly typed textbased language, stupid not to spot the error.
You needed the nodetype = 3, to tell OPENXML that it was the text you wanted, not the xml formatting nodes...

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources