Errors using XML value method

  • I'm having issues using the xml value method to convert an XML COlumn to values and the answers I've tried have not worked. My XML Column appears as follows

    <Transit docid="1" docname="Balance" transittype="0" doccontrol="false" boxed="false">
    <Guid>42702e49b4</Guid>
    </Transit>
    <Transit docid="2" doc_name="Drop Letter" transittype="0" doccontrol="false" boxed="false">
    <Guid>f3db96464e</Guid>
    </Transit>

    I've tried Select transit.value('/Transit/docid)[1]','varchar(50)') as docid from Client and get

    XQuery [creditor.transit.value()]: Syntax error near 'docid'

    I also tried

    Select m.c.value('docid', 'int') as docid

    from Client as s

    outer apply s.transit.nodes('Transit/docid [1]' ) m(c)  and get

    XQuery [Client.transit.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

    I've tried changing datatypes, and editing the value in square brackets but keep getting the same error.  Each Client has as many as 6 of these transit values . I'd appreciate any suggestions about what I'm doing wrong here. Thanks

    • This topic was modified 3 years, 8 months ago by  Paul Stasny.
  • Figured out after lots of trial and error. Result posted in case anyone else trips over the syntax and this helps

    select

    m.c.value('@docid', 'int') as docid,

    m.c.value('@docname', 'varchar(200)') as docname,

    m.c.value('@transittype', 'varchar(200)') as transittype,

    m.c.value('@doccontrol','varchar(20)') as doccontrol,

    m.c.value('@boxed','varchar(20)') as boxed

    from client as s

    outer apply s.transittypes.nodes('Transit') as m(c)

  • This was removed by the editor as SPAM

  • Hamilton wrote:

    However, that's true only when returning the entire XML value. The query() method retrieves a subset of untyped XML from the target XML instance.  location within the document, I would have received a violation error.

    Since the OP didn't explicitly use the query() method, please show us what you mean and why YOU would have received a "violation error".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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