Attribute-centric and Element-centric XML

When working with XML in SQL Server 2005, you will notice that you can either generate an attribute-centric XML or an element-centric XML. In an element centric XML, an element contains the child elements that denote the properties of that element. In an attribute centric XML on the other hand, the same child elements are then present as attributes of that parent element. Here is an example:

SET NOCOUNT ON
— Prepare sample data
DECLARE @table TABLE (Dept_ID INT, EMP_Name VARCHAR(30))

INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Jack’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘John’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Beth’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Mary’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Allen’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Diana’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Don’)

/*Generate attribute centric XML*/
select Dept_ID, EMP_NAME from @table as Dept_EMP for xml auto, Root(‘Dept’)

<Dept>
<Dept_EMP Dept_ID=”10″ EMP_NAME=”Jack” />
<Dept_EMP Dept_ID=”10″ EMP_NAME=”John” />
<Dept_EMP Dept_ID=”10″ EMP_NAME=”Beth” />
<Dept_EMP Dept_ID=”20″ EMP_NAME=”Mary” />
<Dept_EMP Dept_ID=”20″ EMP_NAME=”Allen” />
<Dept_EMP Dept_ID=”20″ EMP_NAME=”Diana” />
<Dept_EMP Dept_ID=”20″ EMP_NAME=”Don” />
</Dept>

/*Generate element centric XML by adding the elements clause*/
select Dept_ID, EMP_NAME from @table as Dept_Emp for xml auto, elements, Root(‘Dept’)

<Dept>
<Dept_Emp>
<Dept_ID>10</Dept_ID>
<EMP_NAME>Jack</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>10</Dept_ID>
<EMP_NAME>John</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>10</Dept_ID>
<EMP_NAME>Beth</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>20</Dept_ID>
<EMP_NAME>Mary</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>20</Dept_ID>
<EMP_NAME>Allen</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>20</Dept_ID>
<EMP_NAME>Diana</EMP_NAME>
</Dept_Emp>
<Dept_Emp>
<Dept_ID>20</Dept_ID>
<EMP_NAME>Don</EMP_NAME>
</Dept_Emp>
</Dept>

One can also chose to combine the element centric and the attribute centric XML. You can do so by adding a nested FOR XML query, example:

SET NOCOUNT ON
— Prepare sample data
DECLARE @table TABLE (Dept_ID INT, EMP_Name VARCHAR(30), Sub_Dept int)

INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (10, ‘Jack’, 1)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (10, ‘John’, 2)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (10, ‘Beth’, 2)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (20, ‘Mary’, 1)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (20, ‘Allen’, 4)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (20, ‘Diana’, 5)
INSERT INTO @table (Dept_ID, EMP_NAME, Sub_Dept) VALUES (20, ‘Don’, 5)

/*Generate a combination of attribute and element centric XML*/
SELECT Dept_ID, Emp_Name,
(SELECT Sub_Dept
FROM @table as Sub_Dept
WHERE Sub_Dept.Dept_ID = Emp_Dept.Dept_ID
and Sub_Dept.Emp_Name = Emp_Dept.Emp_Name
FOR XML AUTO, TYPE, ELEMENTS
)
FROM @table as Emp_Dept
ORDER BY Dept_ID
FOR XML AUTO

<Emp_Dept Dept_ID=”10″ Emp_Name=”Jack”>
<Sub_Dept>
<Sub_Dept>1</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”10″ Emp_Name=”John”>
<Sub_Dept>
<Sub_Dept>2</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”10″ Emp_Name=”Beth”>
<Sub_Dept>
<Sub_Dept>2</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”20″ Emp_Name=”Mary”>
<Sub_Dept>
<Sub_Dept>1</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”20″ Emp_Name=”Allen”>
<Sub_Dept>
<Sub_Dept>4</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”20″ Emp_Name=”Diana”>
<Sub_Dept>
<Sub_Dept>5</Sub_Dept>
</Sub_Dept>
</Emp_Dept>
<Emp_Dept Dept_ID=”20″ Emp_Name=”Don”>
<Sub_Dept>
<Sub_Dept>5</Sub_Dept>
</Sub_Dept>
</Emp_Dept>

Here, you can see that the outer query generated the attribute-centric part and the nested query created the element centric XML. So, now that we have seen and understood the differences between the attribute-centric and element-centric XML, which one should one use? Like most things in IT, the answer is “It Depends” 🙂 since it varies on the implementation. What one is generating, the other program has to consume it and depending upon whether it is an attribute-centric XML or an element-centric XML will change that reading program’s code. In future posts, we will also cover the various ways in which one can read these type of documents using plain simple T-SQL. We had previously touched upon the new nodes() method in our post here and will cover more in the future.

You can read this whitepaper which discusses the differences between elements and attributes and mentions the advantages and disadvantages of the two approaches.