January 23, 2013 at 8:55 am
TABLE tblProduct
ProductId int
Productname varchar(20)
TABLE tblQuantityP
QuantityPId
Quantity int
Price int
ProductId XML
DATA IN tblProduct
ProductId ProductName
1Prod1
2Prod2
3Prod3
4Prod4
5Prod5
6Prod6
DATA IN tblQuantityP
QuantityPId QuantityPriceProductId
11212<itemids> <itemid>1</itemid> <itemid>2</itemid> <itemid>3</itemid></itemids>
21121<itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
31111<itemids> <itemid>222</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
413411<itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>
511211<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?
January 24, 2013 at 1:00 am
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;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply