Errors using XML value method

• Paul Stasny

  Mr or Mrs. 500

  Points: 599

  More actions

  March 11, 2021 at 4:43 am

  Go to Answer

  #3856348

  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 4 years, 2 months ago by Paul Stasny.

• Paul Stasny

  Mr or Mrs. 500

  Points: 599

  More actions

  March 11, 2021 at 5:49 am

  Answer

  #3856366

  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)

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  March 15, 2021 at 6:23 am

  #3857839

  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)