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
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 6:23 am
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy