0

i am just trying to compare two xml and try to store the difference into another variable called @DiffXML but getting error called Incorrect syntax near the keyword 'SET'.

just guide what to fix. thanks

DECLARE @XML1 XML
DECLARE @XML2 XML

DECLARE @DiffXML nvarchar(max)
SET @DiffXML=''

SET @XML1 = 
'<NewDataSet> 
<Employee>
<EmpID>1005</EmpID>
<Name> keith </Name>
<DOB>12/02/1981</DOB>
<DeptID>ACC001</DeptID>
<Salary>10,500</Salary>
</Employee>
</NewDataSet>'

SET @XML2 = 
'<NewDataSet> 
<Employee>
<EmpID>1006</EmpID>
<Name> keith </Name>
<DOB>05/02/1981</DOB>
<DeptID>ACC002</DeptID>
<Salary>10,900</Salary>
</Employee>
</NewDataSet>'

;with XML1 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML1.nodes('/NewDataSet/Employee/*') as T(N)
),
XML2 as
(
  select T.N.value('local-name(.)', 'nvarchar(100)') as NodeName,
         T.N.value('.', 'nvarchar(100)') as Value
  from @XML2.nodes('/NewDataSet/Employee/*') as T(N)
)

SET @DiffXML=(select * from 
(
    select coalesce(XML1.NodeName, XML2.NodeName) as FieldName, 
           XML1.Value as OldValue, 
           XML2.Value as NewValue
    from XML1
      full outer join XML2
        on XML1.NodeName = XML2.NodeName
    where coalesce(XML1.Value, '') <> coalesce(XML2.Value, '')    
) x FOR xml AUTO,elements XSINIL)

print @DiffXML

1 Answer 1

1

Change that from

SET @DiffXML=(select * from

to

SELECT @DiffXML=(select * from

and you will get your result printed

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

1 Comment

your tips worked. i updated my question. please have a look and answer. thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.