Deleting rows with OPENXML

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_DeleteCompanyBusine
ssUnit_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,
WesTry,
DELETE
CompanyBusinessUnit
FROM
CompanyBusinessUnit
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:

> 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_DeleteCompanyBusine
ssUnit_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|||Hi there,
Thanks for answering my post so quickly. Unfortunately your solution did
the same thing as the ones I've tried. It simply deleted the first row
matched from the XML. I have posted my XML below, maybe there is something
wrong with it.
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
Thanks,
Wes
"Alejandro Mesa" wrote:
> Try,
> DELETE
> CompanyBusinessUnit
> FROM
> CompanyBusinessUnit
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
>
> "Wes" wrote:
>|||Try,
DELETE
a
FROM
CompanyBusinessUnit as a
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:
> Hi there,
> Thanks for answering my post so quickly. Unfortunately your solution did
> the same thing as the ones I've tried. It simply deleted the first row
> matched from the XML. I have posted my XML below, maybe there is somethin
g
> wrong with it.
> <NewDataSet>
> <DeletedNodes>
> <CompanyBusinessUnitID>17</CompanyBusinessUnitID>
> <CompanyBusinessUnitID>18</CompanyBusinessUnitID>
> </DeletedNodes>
> </NewDataSet>
> Thanks,
> Wes
> "Alejandro Mesa" wrote:
>|||I thought the problem was with the DELETE statement, but no, it was the
"SELECT ... FROM OPENXML ..." that was selecting just the first row. Can you
try changing this select by:
Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3
AMB
"Alejandro Mesa" wrote:
> Try,
> DELETE
> a
> FROM
> CompanyBusinessUnit as a
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
> "Wes" wrote:
>|||Your firts post will also work if you change the layout of the xml document
to:
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
</DeletedNodes>
<DeletedNodes>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
AMB
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Excellent! Thank you very much as this one works perfectly. Now I'll have
to analyze the code and see what I was doing differently.
Thanks for your help.
Wes
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)

delete table1 from openxml

Hi,
I know how to select using openxml.
Could you please let me have a sample code on how to delete records from a table based on what is inside an xml.
Thanks

Something like:
delete from table1
where
OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)

There are a couple of ways to approach this. One of the easiest ways (again easy but not necessarily the highest performance) is to use a JOIN.

Example :

delete from table1 FROM table1 JOIN
SELECT contractMonth, [date], mid, bizdays

OPENXML (@.idoc, '/marketData/forwardCurves/forwardCurve/maturities/maturity',2)
WITH (
contractMonth varchar(50),
[date] datetime,
mid varchar(50),
bizDays int
)) example1 on table1."commonkey" = example1."commonkey"

|||

You can use OPENXML in FROM clause so do below:

delete from table1

where exists (

select *

from OPENXML(...)

with (...) as x

where x.contractMonth = table1.contractMonth

and x.[date] = table.[date]

...

)