3

Consider the following reasonably simple sql query;

    Declare @ad varchar(3) = 'GBR',
    @date varchar(10) ,
    @time varchar(5),
    @refnum varchar(17),
    @tm varchar(2) = 'CU';

    Set @date = FORMAT(GETUTCDATE(),'yyyy-MM-dd');
    Set @time = FORMAT(GETUTCDATE(),'HH-mm');
    Set @refnum = 'DOM' + FORMAT(GETUTCDATE(),'yyyyMMdd') + '123456';
    WITH XMLNAMESPACES ('http://ec.europa.eu/fisheries/schema/ers/v3' as ers) 


    Select @ad AS '@AD',
    @ad AS '@FR',
    @refnum AS '@ON',
    @date as '@OD',
    @time AS '@OT',

    (Select @tm AS  '@TM' For xml path('ers:DAT'), TYPE)

    For xml path('ers:OPS')
GO

What I was expecting / hoping to get back from this was the following;

    <ers:OPS xmlns:ers="http://ec.europa.eu/fisheries/schema/ers/v3" AD="GBR" FR="GBR" ON="DOM20170411123456" OD="2017-04-11" OT="14-47">
  <ers:DAT  TM="CU" />
</ers:OPS>

Whereas what I in fact got back was ;

    <ers:OPS xmlns:ers="http://ec.europa.eu/fisheries/schema/ers/v3" AD="GBR" FR="GBR" ON="DOM20170411123456" OD="2017-04-11" OT="14-47">
  <ers:DAT xmlns:ers="http://ec.europa.eu/fisheries/schema/ers/v3" TM="CU" />
</ers:OPS>

My question quite simply is why did I get back what I did and what ought I have done to get back what I had hoped for?

TT.
  • 15,774
  • 6
  • 47
  • 88
Dom Sinclair
  • 2,458
  • 1
  • 30
  • 47

1 Answers1

0

This is a known issue (actually it is a feature, voted to be removed) and there is a Connect item opened since 2007. You can check it out here.

A possible workaround is described here.

Rigerta
  • 3,959
  • 15
  • 26