xml - Converting string parsing to XQuery and XPath -

using sql server 2008 r2. i'm trying convert poor xml data stored 1 big string in column. right now, i'm extracting value using string parsing routines i'd use xpath , xquery , want create stored procedure requires instrumentid required input start , end dates optional parameters.

here way i'm doing using t-sql:

use mydb declare @first varchar(15), @second varchar(15),         @datelow datetime, @datehigh datetime,          @instrumentid varchar(15)  set @first = '<instrumentid>' set @second = '</instrumentid>' set @datelow = '2003-04-01'   set @datehigh = '2004-01-13'  set @instrumentid = null set @instrumentid = 'sb2093780001'  select *      (select           out_interface_id, msg_id,           substring(xml_msg, charindex(@first, xml_msg) + len(@first),           charindex(@second, xml_msg) -           charindex(@first, xml_msg) -           len(@first)) instrumentid,          msg_type, xml_msg, cast(xml_msg xml) [quick_xml],           date_received,status, last_modified, environment,          transaction_closed_date               mytable               msg_type in ('abc','def')           , date_received >= @datelow          , date_received < dateadd(day, 1, @datehigh)     ) x      (x.instrumentid = @instrumentid or x.instrumentid = null) order      date_received desc 

and here have tried far using xpath , xquery

use mydb declare @x xml;  select     out_interface_id, msg_id,     cast(xml_msg xml) [quick_xml],     @x.query('//instrumentid') instrumentid,     msg_type, xml_msg,     date_received, status, last_modified, environment,     transaction_closed_date      mytable     msg_type in ('abc','def')  order      date_received desc 

i know @x.query won't work because doesn't know want extract field xml_msg column i've tried far fails. think i'm getting close i've been reading can find on xpath , xquery , can't find specific scenario. xml_msg column untyped, horrible xml 1 long string. maybe have clean before can query it? of extracts field hundreds of lines long after cast xml. appreciated.

xml test data

the xml_msg column varchar(max)

<proponix>     <header>         <destinationid>abc</destinationid>         <senderid>pro</senderid>         <operationorganizationid>abt1</operationorganizationid>         <messagetype>acctv21</messagetype>         <datesent>20160701</datesent>         <timesent>1934</timesent>         <messageid>1091697493</messageid>     </header>         <subheader>             <originalinstrumentid>sb1499780001</originalinstrumentid>                 <splitactivities>                     <instrumentid>sb1499780001</instrumentid>                 </splitactivities>         </subheader> </proponix> 

you see in test xml there originalinstrumentid , instrumentid. i'm concerned instrumentid. originalinstrumentid there types of messages defined in msg_type field.

test

select out_interface_id, msg_id, cast(xml_msg xml).value('//instrumentid[1]','nvarchar(max)') instrumentid mytable order date_received desc 

when try code, following error:

msg 2389, level 16, state 1, line 3 xquery [value()]: 'value()' requires singleton (or empty sequence), found operand of type 'xdt:untypedatomic *'

i've tried lots of different things nothing working.

final working query

it matter of parenthesis.

select out_interface_id, msg_id,     cast(xml_msg xml).value(     '(//instrumentid)[1]','nvarchar(max)')  instrumentid     ,msg_type, xml_msg, cast(xml_msg xml) [quick_xml]      ,date_received,status, last_modified, environment     ,transaction_closed_date mytable     msg_type in ('abc','def')      , date_received >= cast(getdate() -1 date)     , date_received < cast(getdate() date) order date_received desc 

some things think about:

• varchar(max) bad place store xml. not directly casteable xml , not fit special characters. best use real xml or - if there reason keep string - use nvarchar(max). sql server uses utf16 internally , waste lot on casting varchar xml...
• with xml 1 should specific possible. using xpath (//instrumentid)[1] easy type, needs deep search , return unexpected results, if there instrumentids in other places too.

very simple , straight:

declare @tbl table(yourxmlcolumn varchar(max), othervalue varchar(max)); insert @tbl values ( '<proponix>   <header>     <destinationid>abc</destinationid>     <senderid>pro</senderid>     <operationorganizationid>abt1</operationorganizationid>     <messagetype>acctv21</messagetype>     <datesent>20160701</datesent>     <timesent>1934</timesent>     <messageid>1091697493</messageid>   </header>   <subheader>     <originalinstrumentid>sb1499780001</originalinstrumentid>     <splitactivities>       <instrumentid>sb1499780001</instrumentid>     </splitactivities>   </subheader> </proponix>','some other values' ); select tbl.othervalue       ,cast(tbl.yourxmlcolumn xml).value('(//instrumentid)[1]','nvarchar(max)') instrumentid @tbl tbl  

any other value can taken xml easy above.

i'd advise use specific path

select tbl.othervalue       ,cast(tbl.yourxmlcolumn xml).value('(/proponix/subheader/splitactivities/instrumentid)[1]','nvarchar(max)') instrumentid @tbl tbl