XMLCOL.Modify Whats Wrong With This Example?

[Skittle]

I get an error message 'Must declare the table variable "@myDoc"' when I try to edit this XML variable.
I can't see where I'm going wrong. Anybody see what I have got wrong?

declare @myDoc XML;
set @myDoc = 
'<root><ProductDescription ProductID="500"
ProductName="SQL Server 2005">
<Editions>
 <Edition>Standard</Edition>
 <MaximumCPU>4</MaximumCPU>
</Editions>
</ProductDescription>
</root>';
select * from @myDoc;

UPDATE @myDoc
SET xmlcol.modify('delete /root/ProductDescription[@ProductID="500"]');
select @myDoc.value
('(/root/ProductDescription/@ProductID)[1]','int');

Dazed and confused
N+, MCDBA 2000, MCAD .NET

 

[Skittle]

...it's SQL 2005 problem of course )

Dazed and confused
N+, MCDBA 2000, MCAD .NET

 

[mrdenny]

@xmlDoc isn't a table. You can't update it like that.

declare @myDoc TABLE
(xmlcol XML);

insert into @myDoc
select '<root><ProductDescription ProductID="500"
ProductName="SQL Server 2005">
<Editions>
 <Edition>Standard</Edition>
 <MaximumCPU>4</MaximumCPU>
</Editions>
</ProductDescription>
</root>';

select * from @myDoc;

UPDATE @myDoc
SET xmlcol.modify('delete /root/ProductDescription[@ProductID="500"]');

select xmlcol.value
('(/root/ProductDescription/@ProductID)[1]','int')
from @myDoc;

select * from @myDoc;

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Skittle]

Hmm.... another letter of complaint will go to a well known technical book publisher Grrrr!

Thanks dude.



Dazed and confused
N+, MCDBA 2000, MCAD .NET

 

[mrdenny]

Tech books suck. The never check the code only the grammer of the text.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com