'replace value of 'query method

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

SQL Server 2000 Forums

SQL Server Development (2000)

'replace value of 'query method

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-01-23 : 07:08:13

I want to update the value of a specific attribute in an xml fragmet stored in a column. However, I keep getting the following error and cannot for the life of me remember how to fix it:

CREATE TABLE #LineItem 
(LineItemID int, OrderLineID int, Funding xml)

INSERT INTO #LineItem
SELECT 1, 1, '<Supplier_fundings xmlns:dnf="http://www.tesco.com/DNF/">
  <dnf:Supplier_funding value="0">
    <dnf:Discount_amount value="0" />
    <dnf:Supplier_id value="00000" />
  </dnf:Supplier_funding>
</Supplier_fundings>'


SELECT * FROM #LineItem

UPDATE #LineItem
SET Funding.modify('declare namespace dnf="http://www.tesco.com/DNF/"; replace value of (/Supplier_fundings/dnf:Supplier_funding/dnf:Discount_amount/@value[1]) with ("0.01")')
WHERE LineItemID =1

ERROR: Msg 2337, Level 16, State 1, Line 15
XQuery [#LineItem.Funding.modify()]: The target of 'replace' must be at most one node, found 'attribute(value,xdt:untypedAtomic) *'

ANy suggestions???
Thanks

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 07:12:15

Is really XQueury available on SQL Server 2000?

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-01-23 : 07:44:06

we're using SQL Server 2005

I know I've come across this error before, but cannot remember how to fix it!!!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-01-23 : 07:51:18

Ooops, I know what I have done:

/dnf:Discount_amount/@value[1])

Should be

/dnf:Discount_amount/@value)[1]

DOH!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 07:51:22

Oh, my bad.
I thought you meant SQL Server 2000 since this is a SQL 2000 forum and there are other forums regarding SQL Server 2005.

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2007-01-23 : 07:53:26

Oh yeah - another DOH!

I usually use Transact SQL - but somehow have saved this forum to my favourites instead of the one I usually use!

Sorry about that!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources