CODEWITHSUNDEEP
sql-serverxml
Thomas
Thomas

Reputation: 34208

Trying to store result in xml format in variable sql server

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

Upvotes: 0

Views: 62

Answers (1)

Jeff Fritz
Jeff Fritz

Reputation: 9861

Change that from

SET @DiffXML=(select * from

to

SELECT @DiffXML=(select * from

and you will get your result printed

Upvotes: 1

Related Questions