Delete specific node from XML column

  • 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?

  • 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