Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete specific node from XML column Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 8:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:18 AM
Points: 76, Visits: 239
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
Posted Thursday, January 24, 2013 1:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse