XML Comparision - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

XML Comparision

Author

Topic

jayanth_jyothi
Starting Member

11 Posts

Posted - 2009-04-21 : 12:59:45

I have two XML files with the same schema stroed in two different columns, i need to find out the nodes with different values in two XML files

For Example I have Table called tblXMLCompare (Col1 XML,Col2 XML)

insert into tblXMLCompare (
'<CompanyDetails>
<Company>
<CompanyID>100</CompanyID>
<CompanyName>ABC Company</CompanyName>
<CompanyPhone>123-345-4567</CompanyPhone>
</Company>
</CompanyDetails>'
,

'<CompanyDetails>
<Company>
<CompanyID>100</CompanyID>
<CompanyName>ABC Company</CompanyName>
<CompanyPhone>234-456-5678</CompanyPhone>
</Company>
</CompanyDetails>')

I need to compare Col1 & Col2 and should get the output with below values

ColumnName OldValue NewValue
---------- --------- ---------
CompanuPhone 123-345-4567 234-456-5678

Thanks in Advance

Jayanth

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-21 : 13:26:45

something like this as a start - you can play around with it. XML always eats up time.

select case when convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyID')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyID')) then 'CompanyName' end ,
case when convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyName')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyName')) then 'CompanyName' end ,case when convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyPhone')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyPhone')) then 'CompanyPhone' end
from tblXMLCompare
where convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyID')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyID'))
or convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyName')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyName'))
or convert(varchar(max),col1.query('/CompanyDetails/Company/CompanyPhone')) <> convert(varchar(max),col2.query('/CompanyDetails/Company/CompanyPhone'))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-21 : 13:40:47

Um - does that work on v2005? I forget and don't have one available at the moment.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jayanth_jyothi
Starting Member

11 Posts

Posted - 2009-04-21 : 14:04:08

Is it some thing can we make it generic i.e XML files in Col1 & Col2 will be different in each record

Example:

Col1 Col2
---- ----
<CompanyXML1> <CompanyXML2>
<EmployeeXML1> <EmployeeXML2>

so result shoud be like below

XMLType ColumnName OldValue NewValue
------- ---------- -------- --------
Company CompanyPhone 123-345-4567 234-456-6789
Employee ManagerName Nancy Peter

Thanks in Advance
Jayanth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources