Tag : xml

Select all elements regardless of level in XML

We have already published a post on “Read XML Data as a table in SQL Server” and “Read and compare XML nodes dynamically with unknown elements“. In this post, we will see that how we can skip levels in XML string to read all the elements at a given level regardless of the level name. A wildcard character * (asterisk) can be used to extract all the elements of a XML string regardless of the level name. Lets have a demo to understand this.

Suppose, we have below XML data:

<EmpName>Employee 1</EmpName>
<EmpName>Employee 2</EmpName>
<EmpName>Employee 3</EmpName>
<EmpName>Employee 4</EmpName>
<EmpName>Employee 5</EmpName>

In this data, the root level node is <Department> which has <HR>, <Admin>, and <Engineering> as their children … More

Convert rows into comma separated values in a column 9

In this post, we are going to learn a technique to combine all the values of a row (based on a particular condition) with a separator, in a column along with other columns. What i meant by this let me explain it in more detail. Have a look on below question as raised by a colleague;

I have a Students table and a Subjects table. One student has been assigned to multiple Subjects in a transaction table. I want to extract the name of all the Subjects for which the student is assigned in a comma separated way along with other details of the student. If student is not assigned yet, i want to show a “Not Assigned Yet” value for those records.

To achieve this task, i have created a demo script and sharing the same with all the details. Below you can find the table structures and demo … More

Read and compare XML nodes dynamically with unknown elements 1

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 … More

Read XML Data as a table in SQL Server 1

Sometimes we need bulk data to be sent to SQL Server in procedure parameter and inside procedure we perform some insert or update or delete or even another select with joins etc. And in such type of scenarios, we prefer to send these bulk data in XML format to SQL Server. So in this post we will learn, how we can read xml data in a table. Proceeding with “Read XML data as a table”,  first of all we will create a demo XML variable to perform the select operation on it.

Read XML Data

XML Data

Now we have to read this data in a table to be used further, To get that first have a look on the below select statement;

tbl.col.value('ID[1]', 'smallint'),
Tbl.Col.value('Name[1]', 'varchar(100)'),
Tbl.Col.value('Age[1]', 'smallint'),
Tbl.Col.value('Gender[1]', 'varchar(10)'),
Tbl.Col.value('City[1]', 'varchar(50)'),
Tbl.Col.value('State[1]', 'varchar(50)')

FROM   @xml.nodes('/DataTable/Employee') tbl(col)

This select statement will output as below;

Read XML Data

XML as Table

Now we can put … More