Read and compare XML nodes dynamically with unknown elements

In this article, I will share a practical example from my experience. In one of my project, I had a requirement of dynamic data comparison between two entities and the number of attributes were dynamic and can be added any time by end user and removed too. Let me explain it with an example that how we can “Read XML with unknown elements”;

Problem explanation with example:-

We have employees as an entity with many attributes like “salary”, “employee group”, “employment type”, “plan”, “Is owns a Car”, “Gender”, “city” and so on….. and end user wants to create policies with some combination of attributes defined to get list of employees satisfying those criterias defined in the policy.

Policy name – Test_Policy

Attributes for policy – Regular employee, plan A, Having Car, Belongs to New Delhi, India

Above is just an example. It may be in next policy end user can accommodate many more attributes or less. End user can create a combination of any number of attributes as per available list for his/her policy.

All employees have also these attributes which is extendable how we can store them in XML and compare them dynamically.

If we store these attributes as columns and compare them dynamically using dynamic sql, each time a new attribute will be introduced, you must have to make changes accordingly in your tables schema.

To avoid any change in table schema, here i would like to use XML to store and compare these dynamic data.

Creating demo data and temporary tables:-

CREATE TABLE #tbl_Policy
(
PolicyID INT IDENTITY(1,1),
PolicyDefinition XML
)

Insert demo data as below;

INSERT INTO #tbl_Policy(PolicyDefinition)
VALUES('<Rule>
<EmpType>Regular</EmpType>
<Plan>A</Plan>
<HaveCar>Y</HaveCar>
<City>New Delhi</City>
</Rule>')

Now create a table for employee details;

CREATE TABLE #tempEmployee
(
ID INT IDENTITY(1,1),
Name VARCHAR(100),
EmpAttributes XML
)

Insert data for employees as below;

INSERT INTO #tempEmployee(Name, EmpAttributes)
VALUES('Employee 1', '<EmpAttributes>
<EmpType>Regular</EmpType>
<Plan>A</Plan>
<HaveCar>Y</HaveCar>
<City>New Delhi</City>
<Country>India</Country>
<Gender>Male</Gender>
</EmpAttributes>'),
('Employee 2', '<EmpAttributes>
<EmpType>Regular</EmpType>
<Plan>B</Plan>
<HaveCar>N</HaveCar>
<City>New Delhi</City>
<Country>India</Country>
<Gender>Male</Gender>
</EmpAttributes>'),
('Employee 3', '<EmpAttributes>
<EmpType>Contract</EmpType>
<Plan>C</Plan>
<HaveCar>Y</HaveCar>
<City>Hyderabad</City>
<Country>India</Country>
<Gender>Male</Gender>
</EmpAttributes>'),
('Employee 4', '<EmpAttributes>
<EmpType>Regular</EmpType>
<Plan>A</Plan>
<HaveCar>Y</HaveCar>
<City>New Delhi</City>
<Country>India</Country>
<Gender>Female</Gender>
</EmpAttributes>')

Below is the data which has inserted in tables;

SELECT * FROM #tempEmployee
SELECT * FROM #tbl_Policy

Reading XML Nodes dynamically

Just have a look on above picture, from that we are going to extract all the details stored in xml.

Below query extracts policy detail from policy table dynamically with unknown number of xml elements and nodes;

SELECT
tbl.col.value('local-name(.)','VARCHAR(100)') AS Name,
tbl.col.value('.[1]','VARCHAR(14)') AS Value
FROM #tbl_Policy
CROSS APPLY #tbl_Policy.PolicyDefinition.nodes('/Rule/*') AS tbl(col)

SELECT
NAME,
tbl.col.value('local-name(.)','VARCHAR(100)') AS Name,
tbl.col.value('.[1]','VARCHAR(14)') AS Value
FROM #tempEmployee
CROSS APPLY #tempEmployee.EmpAttributes.nodes('/EmpAttributes/*') AS tbl(col)

Comparing xml data dynamically with unknown number of nodes:-

;WITH CTEPolicy AS
(
SELECT
tbl.col.value('local-name(.)','VARCHAR(100)') AS PolicyAttName,
tbl.col.value('.[1]','VARCHAR(14)') AS PolicyAttValue
FROM #tbl_Policy
CROSS APPLY #tbl_Policy.PolicyDefinition.nodes('/Rule/*') AS tbl(col)
WHERE PolicyID = 1
),
CTEEMPList AS
(
SELECT *
FROM
(
SELECT
Name AS EmpName,
tbl.col.value('local-name(.)','VARCHAR(100)') AS EmpAttName,
tbl.col.value('.[1]','VARCHAR(14)') AS EmpAttValue
FROM #tempEmployee
CROSS APPLY #tempEmployee.EmpAttributes.nodes('/EmpAttributes/*') AS tbl(col)
)DTEmp
INNER JOIN
(
SELECT PolicyAttName, PolicyAttValue FROM CTEPolicy
)DTPloicy
ON DTEmp.EmpAttName = DTPloicy.PolicyAttName AND DTEmp.EmpAttValue = DTPloicy.PolicyAttValue
)
SELECT
DISTINCT EmpName
FROM CTEEMPList CTLEmpMain
WHERE (SELECT COUNT(1) FROM CTEEMPList CTL WHERE CTL.EmpName = CTLEmpMain.EmpName) = (SELECT COUNT(1) FROM CTEPolicy)

In above query, i have used nested CTE to stage the data temporarily. You can also create table variables or temp tables to achieve this but i prefer nested CTE in such type of scenarios, becuase the entire logic can be handled in one statement.

Conclusion

I have compared the xml data dynamically and then put a logic to compare the number of attributes satisfying for each employee and if count of attributes of the policy is equals to the matched policy attributes, name of employee returned as a final output.

If you like this article “Read XML with unknown elements”, please don’t forget to share your feedback.