Delete specific node from XML column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Delete specific node from XML column

Delete specific node from XML column

Rate Topic

Display Mode

Topic Options

Author

Message

thbaig1

Posted Wednesday, January 23, 2013 8:55 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 4:26 AM
Points: 60, Visits: 174

TABLE tblProduct
ProductId int
Productname varchar(20)

TABLE tblQuantityP
QuantityPId
Quantity int
Price int
ProductId XML

DATA IN tblProduct
ProductId ProductName
1 Prod1
2 Prod2
3 Prod3
4 Prod4
5 Prod5
6 Prod6

DATA IN tblQuantityP
QuantityPId Quantity Price ProductId
1 12 12 <itemids> <itemid>1</itemid> <itemid>2</itemid> <itemid>3</itemid></itemids>
2 11 21 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
3 11 11 <itemids> <itemid>222</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
4 134 11 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
5 112 11 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>

Now I can JOIN XML COLUMN TO mother TABLE TO GET DATA AS following . (suggest IF SOME more optimized way: Not real question)
SELECT qp.QuantityPId ,
Prod.ProductId ,
Prod.ProductName
FROM dbo.tblQuantityP qp
CROSS APPLY ProductId.nodes('itemids/itemid') AS xmlitems ( itemid )
JOIN dbo.tblProduct Prod ON Prod.ProductId = xmlitems.ProductId.value('.',
'int');

My Question is can I DELETE DATA FROM tblQuantityP.ProductId in a way IF parent TABLE has NOT record against it ?
e.g. Prod1 with ProductId 1 removed from tblProduct, can I DELETE ONly ProductId 1 FROM ALL occurance IN tblQuantityP.ProductId?

Post #1410644

Bhuvnesh

Posted Thursday, January 24, 2013 1:00 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

thbaig1 (1/23/2013)

can I DELETE DATA FROM tblQuantityP.ProductId in a way IF parent TABLE has NOT record against it ?
e.g. Prod1 with ProductId 1 removed from tblProduct, can I DELETE ONly ProductId 1 FROM ALL occurance IN tblQuantityP.ProductId?

Something like this ?

delete p from tblQuantityP Where exists (select 2 from tblProduct t where t.ProductId = p.ProductId and p.ProductId = 1)

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1410958

« Prev Topic | Next Topic »

Permissions