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:
>

Deleting dataset

Sorry if this post appears twice, but I couldn't see it posted last time.

Does anyone know an easy way (read not by going into the XML) of deleting a dataset?

Silly me! I missed the "Delete this dataset" button on the toolbar. Just ignore me and I'll go back to sleep!

Delete with Updategrams

Hi,
Is it posible to delete all records from table (like T-SQL: Delete From
MyTable) using XML Updategrams ?
Thanks,
Marcin
Updategrams aren't meant for batch operations like that. To do it you would
first have to select all of the data out and put it into the "before" block,
and then omit the "after".
Irwin
"Marcin" <mwieckow@.onet.pl> wrote in message
news:ui6sGWVKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it posible to delete all records from table (like T-SQL: Delete From
> MyTable) using XML Updategrams ?
> Thanks,
> Marcin
>

Delete with Updategrams

Hi,
Is it posible to delete all records from table (like T-SQL: Delete From
MyTable) using XML Updategrams ?
Thanks,
MarcinUpdategrams aren't meant for batch operations like that. To do it you would
first have to select all of the data out and put it into the "before" block,
and then omit the "after".
Irwin
"Marcin" <mwieckow@.onet.pl> wrote in message
news:ui6sGWVKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it posible to delete all records from table (like T-SQL: Delete From
> MyTable) using XML Updategrams ?
> Thanks,
> Marcin
>

Delete Takes Time when u have text datatype column

I have a table with a coulumn of Text Datatype. This column is stored with XML data. Now i need to delete records from this table using a SP. The deletion 60K records is taking 30 min almost. Generally this table will not have this much deletion. But when there is no Text data type column (i removed the column from the table for testing) the same deletion is taking only few sec. What is the techinical reason behind it.

If anyone could explain this it would be really helpful

Leena

See Text datatype in Books Online. It is one of the BLOB datatype, it depends with the option you set in the table for large objects.

Micrsoft recommand to switch over from Text/Ntext to Varchar(Max)/NVarchar(Max) - if you use sql server 2005.

|||

It really is all depending on the size of the data in your text datatype. Data is stored in ~8K pages, and if you have 80K worth of text in columns, it will require 10 pages to be deleted to delete the row. Your table will look something like (and this can get ugly for 60K rows):

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

[DataPage] -> [TextPage]-> [TextPage]-> [TextPage]-> [TextPage]

And all of the pages would have to be deleted. I think this might be improved upon performance wise in 2005, but I am not 100% sure. There is a concept of Ghost rows that may apply here where pages are simply marked as deleted. You should also try using the varchar(max) datatype if at all possible, though it has the same issues.

|||

thanks mani and louis for quick help.

one more q..... is sp_spaceused reflect the text data storage space also. If not how can we findout that.

the table with Text data and with out text data is almost showing same space used. Why its so..

Thanks again

Leena

|||

The large datatype value is stored outside of your table row & your current tables row's keeping the pointer of the outside stored values. To change this settins use the following statement

Exec sp_tableoption N'MyTable', 'large value types out of row', OFF

Exec sp_tableoption N'MyTable', 'text in row', ON

|||

When the BLOB's are not stored in row you have a lot of random I/O going on too.

Random I/O are very expensive of course. It has to check for each record where the BLOB data is located, remove it, go to the next record, check where the BLOB data is located, remove it, ... well you get my point I hope :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

delete output into <xml column of audit table>

Currently running Sql Server 2005
Is it possible to issue the delete command and capture the affected rows as
xml types that will be stored in an audit table with an xml column?
Something along the lines of:
delete from source_table
output
(deleted.*
into audit_table (xml_audit_column)
for xml auto)
where source_table.column = @.delete_valueYou cannot do it in a single statement because the OUTPUT clause does not
support subqueries. One way is to get the deleted rows into a temp table and
then transfer as XML to your audit table.
Here is one example of implementing this in a trigger:
http://blogs.sqlservercentral.com/prosqlxml/archive/2008/01/26/create-a-dynamic-logging-trigger-with-xml.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com