XML DML - Delete and Insert - 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

SQL Server Development (2000)

XML DML - Delete and Insert

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-10-24 : 09:49:17

I have a procedure that takes in an XML blob and uses this information to edit an xml column. However, I have come across a couple of issues:

USING MODIFY('DELETE...')

		UPDATE bms.CustomerBasket
		SET ProductXML.modify('delete //OrderLine[1]')
		WHERE CustomerID = @CustomerID
		AND @ProductXML.value('(//OrderLine/@CatalogueItemReference)[1]', 'varchar(20)' )
		= ProductXML.value('(//OrderLine/@CatalogueItemReference)[1]', 'varchar(20)' )
		AND COALESCE(@ProductXML.value('(//OrderLine/@ParentCatalogueItemReference)[1]', 'varchar(20)' ),'Void')
		= COALESCE(ProductXML.value('(//OrderLine/@ParentCatalogueItemReference)[1]', 'varchar(20)' ),'Void')

This code is supposed to remove the OrderLine from the XML Column where it has the same catalogueITemReference and ParentCatalogueItemReference as that contained in the XML variable. However, the WHERE clause is not doing anything.

How can I filter the results based on the data in the xml variable???

I've tried the following, and that removes all the nodes if the matching attributes are on the first node?????

		UPDATE bms.CustomerBasket
		SET ProductXML.modify('delete //OrderLine')
		FROM bms.CustomerBasket
		CROSS APPLY @ProductXML.nodes('//OrderLine') as T1(pd)
		WHERE CustomerID = @CustomerID
		AND T1.pd.value('(//OrderLine/@CatalogueItemReference)[1]', 'varchar(20)' )
		= ProductXML.value('(//OrderLine/@CatalogueItemReference)[1]', 'varchar(20)' )
		AND COALESCE(T1.pd.value('(//OrderLine/@ParentCatalogueItemReference)[1]', 'varchar(20)' ),'Void')
		= COALESCE(ProductXML.value('(//OrderLine/@ParentCatalogueItemReference)[1]', 'varchar(20)' ),'Void')

Any assistance will be greatly appreciated

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-10-24 : 11:56:52

Here is what I want to do in a more simpler form:

create table T
(id int, xmlCol xml)

truncate table T
insert into T
select 1,'<OrderLines><OrderLine CatalogueItemRef = "1a"/><OrderLine CatalogueItemRef = "1b"/></OrderLines>'
insert into T
select 2,'<OrderLines><OrderLine CatalogueItemRef = "1c"/><OrderLine CatalogueItemRef = "1d"/></OrderLines>'

select * from T

declare @id int
set @id = 1

declare @T xml
set @T = '<OrderLines><OrderLine CatalogueItemRef = "1a"/><OrderLine CatalogueItemRef = "1b"/></OrderLines>'

update dbo.T
set xmlCol.modify('delete //OrderLine')
where id = @id
and @T.value('(//OrderLine/@CatalogueItemRef)[1]', 'varchar(20)' )
= xmlCol.value('(//OrderLine/@CatalogueItemRef)[1]', 'varchar(20)' )

select * from T

I want to delete only the one OrderLine node, without having to state the position of the node xmlCol.modify('delete //OrderLine')[1] as I will not have this information.

Heeeeeeelp

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources