Xquery question

Question

Post reply

Xquery question

sql_prodigy

SSCrazy

Points: 2254
More actions
August 25, 2008 at 3:43 am

#191810

there are 2 types of movement xml objects in my XMLCollection table, Acquire and Purchase. I want to exclude Acquire in my resultset.
This is what i have currently but its throws an exception
value() requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
SELECT
XML.value('(/*[local-name()="Movement"][@Context="Purchase"])','varchar(50)') AS Purchase
from
XMLCollection
any help appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sql_prodigy SSCrazy Points: 2254 More actions · Answer 1

or how can i retrieve the Context attribute value where its equal to Acquire ??

Quatrei.X SSCrazy Points: 2697 More actions · Answer 2

Hi there,

hope this helps

DECLARE @x XML, @xdoc INT

-----------------------------------------------------------------------------------------

-- OVER HERE!!!

--- Dear admins, there seems to be a glitch when entering XML code here in the forums.

--- please fix it when possible. thank you.

--- Ok, the xml below as you would notice does not have any<>... CAUSE i removed them...

--- WHY? every time i save this, it reads the xml data not as ordinary text

--- and my xml script does not appear, as in pure blank. try to post one yourself

--- I don't intend to be rude, but please fix it when posible thanks ^__^

--- we all experience glitches sometimes

--- for the ones who will use the code, just put the <> where they should be before executing

------------------------------------------------------------------------------------------

SET @x=

' ROOT

Movement ID="1" Context="Acquire"/

Movement ID="2" Context="Acquire"/

Movement ID="3" Context="Purchase"/

Movement ID="4" Context="Acquire"/

Movement ID="5" Context="Purchase"/

Movement ID="6" Context="Purchase"/

Movement ID="7" Context="Acquire"/

Movement ID="8" Context="Purchase"/

/ROOT

'

SELECT @x

EXEC sp_xml_preparedocument @xdoc OUT, @x

SELECT ID,Context

FROM OPENXML (@xdoc,'/ROOT/Movement',2)

WITH (ID INT '@ID', Context VARCHAR(10) '@Context')

WHERE Context<>'Acquire'

EXEC sp_xml_removedocument @xdoc

if you need more information aboutthe xml prcedures or keywords that i used, you can check it out at http://www.MSDN.com

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 3

StarNamer

SSCrazy Eights

Points: 8746

September 3, 2008 at 8:21 am

#866502

To get < and > just use < and >

Derek

Mark Cowne One Orange Chip Points: 26951 More actions · Answer 4

SELECT

XML.value('(/*[local-name()="Movement"][@Context="Purchase"])[1]','varchar(50)') AS Purchase

from

XMLCollection

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537