How to retrieve values of elements in this query (SQL XML)

I have this query

 set quoted_identifier off
 declare @sName varchar(15); select @sName='tdsls4401m000'
 declare @p table(target varchar(100),type varchar(10),val varchar(100),[order] float)
 declare @v table(verb varchar(10),field varchar(20))

 insert @p(target,type,val,[order])
   select "/session/verbs/put[@field='orno.f']",'string',141527,1
        union   select "/session/verbs/put[@field='orno.t']","string",141527,2
        union   select "/session/verbs/put[@field='orno.t']","string",141527,2
        union   select "/session/verbs/put[@field='comp.f']","string",000,2
        union   select "/session/verbs/put[@field='comp.t']","string",999,2.1
        union   select "/session/verbs/setreport/pa开发者_如何学Pythonrameter[@name='device']","string",105,3
 insert @v(verb,field)
   select 'put','orno.f'
   union    select 'put','orno.t'

set quoted_identifier on

SELECT  (SELECT target, type, val
         FROM @p
         FOR XML RAW('parameter'), TYPE, ROOT('parameters'))

for xml raw('session')

and this returns

<session>
  <parameters>
    <parameter target="/session/verbs/put[@field='comp.f']" type="string" val="0" />
    <parameter target="/session/verbs/put[@field='comp.t']" type="string" val="999" />
    <parameter target="/session/verbs/put[@field='orno.f']" type="string" val="141527" />
    <parameter target="/session/verbs/put[@field='orno.t']" type="string" val="141527" />
    <parameter target="/session/verbs/setreport/parameter[@name='device']" type="string" val="105" />
  </parameters>
</session>

I want the val to be returned like this

<parameter target="/session/verbs/put[@field='comp.f']" type="string">0</parameter>

Try something like this - you need to use FOR XML PATH() instead of FOR XML RAW, because with FOR XML PATH, you can define whether a value is rendered as attribute on the XML tag, as XML element, or as text value inside the XML tag:

SELECT  
   (SELECT 
      target AS '@target', 
      type AS '@type', 
      val AS 'text()'
    FROM @p
    FOR XML PATH('parameter'), TYPE, ROOT('parameters')
   )
FOR XML RAW('session')

This gives me:

<session>
  <parameters>
    <parameter target="/session/verbs/put[@field='orno.f']" type="string">141527</parameter>
    <parameter target="/session/verbs/put[@field='orno.t']" type="string">141527</parameter>
    <parameter target="/session/verbs/put[@field='orno.t']" type="string">141527</parameter>
    <parameter target="/session/verbs/put[@field='comp.f']" type="string">0</parameter>
    <parameter target="/session/verbs/put[@field='comp.t']" type="string">999</parameter>
    <parameter target="/session/verbs/setreport/parameter[@name='device']" type="string">105</parameter>
  </parameters>
</session>