Systems Engineering and RDBMS

Comparing two XML values/variables

Posted by decipherinfosys on January 23, 2009

So far in most of our posts related to XML (be it Oracle or SQL Server), we have talked mostly about how to retrieve data in XML of how to take XML data and do the insert, update operations as well as apply filters to retrieve only sub-set of the data from an XML document.

We recently had a need to compare two XML variables.  The need was pretty simple actually – if both of them matched in structure as well as data, we needed to run a particular process – if they did not match, we needed to log a record into the MSG_LOG table.  So, if it were only an issue of comparing the structure, that could very easily be achieved by using an XML SCHEMA COLLECTION like we had shown over here.   In this case, we could then do validations for both the variables/values against that schema to see if they match.  Over here though, we have to not only make comparisons for the XSD but also the data.

For the sake of this example, let’s create three sample xml variables:  Here is the XML Schema Collection that we will use.

CREATE XML SCHEMA COLLECTION HR AS
‘<?xml version=”1.0″?>
<xsd:schema
xmlns: xsd=”http://www.w3.org/2001/XMLSchema”&gt;
<xsd:element name=”EmpDept”>
<xsd:complexType>
<xsd:sequence>
<xsd:element name=”EmpName”>
<xsd:complexType>
<xsd:attribute name=”name”
type=”xsd:string” />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name=”name”
type=”xsd:string” />
</xsd:complexType>
</xsd:element>
</xsd:schema>’
GO
DECLARE @xml1 xml(HR), @xml2 xml(HR), @xml3 xml(HR)
SET @xml1 =
‘<EmpDept name=”HR”>
<EmpName name=”Donald” />
</EmpDept>’

SET @xml2 =
‘<EmpDept name=”HR”>
<EmpName name=”Richard” />
</EmpDept>’

SET @xml3 =
‘<EmpDept name=”HR”>
<EmpName name=1234 />
</EmpDept>’

Out of the three variables from above, the third one is the one that does not conform to the XML schema.  So, when you run this, you will get an error for the third one:

Msg 9413, Level 16, State 1, Line 12
XML parsing: line 2, character 15, A string literal was expected

So, for @xml1 and @xml2 we have now established that they match XSD-wise.  Now, let’s compare @xml1 and @xml2 to check on the data comparison – we will use the nodes method to de-construct the XMLs and then do the join based on the department and then compare the names of the employees – this is to just illustrate the point – it can easily be extended to other such scenarios:

select
XML1.Emp_Name    as XML1_EMP_NAME
,XML2.Emp_Name    as XML2_EMP_NAME
from
(select
x.i.value(‘@name[1]’, ‘nvarchar(50)’) as Dept_Name
,x.i.value(‘EmpName/@name[1]’, ‘nvarchar(50)’) as Emp_Name
from @xml1.nodes(‘//EmpDept’) as x(i)) as XML1
inner join
(select
x.i.value(‘@name[1]’, ‘nvarchar(50)’) as Dept_Name
,x.i.value(‘EmpName/@name[1]’, ‘nvarchar(50)’) as Emp_Name
from @xml2.nodes(‘//EmpDept’) as x(i)) as XML2
on XML1.Dept_Name = XML2.Dept_Name
where XML1.Emp_Name <> XML2.Emp_Name

And this is the output that you will get when you execute this code:

XML1_EMP_NAME                                      XML2_EMP_NAME
-------------------------------------------------- --------------------------------------------------
Donald                                             Richard

So, using this methodology we can compare two XML variables/values for both structure as well as data comparisons.  In one of the future posts, we will look into making this generic so that we can then pass in two XML parameters in a stored procedure and the output should list out the differences.  It involves looping constructs since in the case of a generic piece of code, we won’t know the XML nodes and elements/attributes.

One Response to “Comparing two XML values/variables”

  1. […] c) value() method: This method allows us to perform XQuery on a xml instance and return a scalar value back.  One of the posts in which we had covered this was the one where we had compared two different XML variables – here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: