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
 General SQL Server Forums
 New to SQL Server Programming
 XML update question

Author  Topic 

fungron
Starting Member

5 Posts

Posted - 2013-02-20 : 13:44:28
Dear all
This is my table structure

use sale3
GO

CREATE TABLE Product
(
ProductCode nchar(10) not null,
Description nvarchar(500),
STDUnitCost numeric(11,4),
STDUnitCostU nvarchar(5),
STDUnitPrice numeric(11,4),
STDUnitPriceU nvarchar(5),
ValueBalance xml DEFAULT '<value />' NOT NULL,
QtyBalance xml DEFAULT '<qty />' NOT NULL
)

INSERT INTO [Product]
([ProductCode])
VALUES
('AB250')

INSERT INTO [Product]
([ProductCode])
VALUES
('AB100')

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="ddd" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB250'


UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="TED" />
<Period id="122012" />
<StockValue id="10" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'

UPDATE [Product]
SET ValueBalance.modify('
insert
<Row>
<LocationCode id="WHD" />
<Period id="122012" />
<StockValue id="20" />
<StockQty id="10" />
</Row>
as last
into (/value)[1]
')
WHERE ProductCode='AB100'

------------------------------
What is the statement to update stockQty id=100
where ProductCode='AB100' LocationCode id="WHD" Period id="122012"

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 15:11:50
[code]UPDATE Product SET
ValueBalance.modify('
replace value of
(/value/Row[LocationCode/@id="WHD"][Period/@id="122012"]/StockQty/@id)[1]
with "100"')
WHERE ProductCode = 'AB100'
[/code]
Go to Top of Page

fungron
Starting Member

5 Posts

Posted - 2013-02-20 : 22:11:49
It works find, thank you so much
Go to Top of Page
   

- Advertisement -