How to shape rename sql.xml query xml elements

Question

I have the following query

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/parameter[@name='device']","string",105,3

set quoted_identifier on
SELECT  (SELECT target, type
         FROM @p

         FOR XML RAW, TYPE,ROOT('session')).query('/*');

and this returns

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

How can I modify the query to rename <row> to <parameter>

Why do you want to? Wouldn't it be easier to adjust the part that consumes the XML that the elements are called <row>? — Tomalak
– Tomalak, Commented Dec 18, 2010 at 12:29
@Tomolak this is just the begining, I will need to add many more child elemements to this.. it perhaps makes sense to refer /session/parameters/parameter /session/verbs/verb[@setreport]/parameter/... than /session/row/row.. etc.. — TonyP
– TonyP, Commented Dec 18, 2010 at 12:32

ASpirin · Accepted Answer · 2010-12-18 12:31:13Z

2

if you user SQL Server 2008 (R2) you may pass your parameter name after RAW derective:

FOR XML RAW('parameter'), TYPE,ROOT('session')).query('/*');

answered Dec 18, 2010 at 12:31

ASpirin

3,6711 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TonyP Over a year ago

Thanks Aspirin, how is your blood thickness ?(:-

Collectives™ on Stack Overflow

How to shape rename sql.xml query xml elements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related