Why is the namespace definition appearing in the inner element of this query as well as the outer

Consider the following fairly 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 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>

      

While I really came back,

    <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 is simple enough, why did I get back what I did, and what should I have done to get back what I was hoping for?

+3


source to share


1 answer


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



A possible workaround is described here .

0


source







All Articles