I am trying to generate
SELECT DISTINCT
P.DOMAIN_ID,
P.SOURCE_SYSTEM_ID
FROM EDW.dbo.DOMAIN_VALUE AS P
WHERE P.ID = 4
AND CURRENT_FLAG = 'Y'
EXCEPT
( SELECT F.DOMAIN_ID,
F.SOURCE_SYSTEM_ID
FROM EDW.dbo.DOMAIN AS F
WHERE F.ID = 4
AND F.CURRENT_FLAG = 'Y'
)
FOR XML PATH('DOMAIN'),
ROOT('DOMAIN_VALUE')
The output value in XML in Result tab as
<REFERENCE_DOMAIN_VALUE>
<REFERENCE_DOMAIN>
<REFERENCE_DOMAIN_ID>10799</REFERENCE_DOMAIN_ID>
<REFERENCE_SOURCE_SYSTEM_ID>7452-001</REFERENCE_SOURCE_SYSTEM_ID>
</REFERENCE_DOMAIN>
</REFERENCE_DOMAIN_VALUE>
Now I need to convert this XML out to varchar(max) but the result needs to be same.