Xquery question

  • 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

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

  • 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
  • To get < and > just use &lt; and &gt;

    Derek

  • 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

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

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