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