Errors using XML value method

Question

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 3 years, 8 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)
March 15, 2021 at 3:59 am

This was removed by the editor as SPAM
March 19, 2021 at 2:53 pm

This was removed by the editor as SPAM
April 26, 2021 at 6:47 am

This was removed by the editor as SPAM
May 13, 2021 at 2:48 pm

This was removed by the editor as SPAM
May 28, 2021 at 11:52 am

This was removed by the editor as SPAM
September 8, 2021 at 1:48 pm

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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)