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 3:59 am
This was removed by the editor as SPAM
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.
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