FOR XML PATH Pivot Columns as Key Value Pairs defeating the "must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH" error

Yesterday I had been working in SQL to create an xml document from a query I needed to make key value pairs out of my column list using one attribute as the key and the element content as the value. Pretty common pattern in XML right? I am aware of and have used the PIVOT key word however using it to describe what I am doing here seemed like the best choice of overloaded term. After several of failed attempts and some googling I threw the following query below into the management studio and finally got the output I had been looking for. The solution? simply add a '' between your elements telling sql server that you have a whitespace value between the two elements.

Transaction was deadlocked on resources with another process and has been chosen as the deadlock victim on page and primary key using Microsoft Sql Server

Background

There are many possibilities for deadlocks to occur, this post specifically covers a condition I encountered where a table primary key and page lock on the same table created a deadlock error. I am not going to go into the specifics of troubleshooting deadlocks chances are if you have made it here you already know about using SQL Server Profiler. Deadlocks occur when two pid's are trying to use each others locked object at the same time. The most common cause I have encountered for deadlocks is timing, the longer the transaction or operation the higher the risk that another concurrent operation will create a deadlock situation.

Example

I encountered the deadlock below when a new table in the system began to grow quickly. The table in question was used in frequent and concurrent read/write operations. This situation is probably one that is frequently encountered and the solution in my case turned out to be very simple. Figure A can be distilled to the following sql:

CREATE TABLE A
(
 Id int IDENTITY PRIMARY KEY
)
CREATE TABLE B
(
 Id int IDENTITY PRIMARY KEY,
 AId int FOREIGN KEY REFERENCES A(Id)
)
CREATE TABLE C
(
 Id int IDENTITY PRIMARY KEY,
 BId int FOREIGN KEY REFERENCES B(Id)
)
CREATE TABLE D
(
 Id int IDENTITY PRIMARY KEY
 AId int FOREIGN KEY REFERENCES A(Id),
 CId int FOREIGN KEY REFERENCES C(Id),
)
//Insert some record into A,B,C,D ...//

//Left oval in figure A This is the deadlock victim//
DELETE B WHERE Id=1

//Right oval in figure B//
DELETE C WHERE Id=1

Explanation

You might have expected the statements above to contain a delete cascade option there aren't any. If you are looking for indexes on any there would only be the primary key created by SQL server. Which leads me to the root cause of this issue, table D grew to 500K+ rows which is about where the adventure began. As I mentioned earlier the common cause I have encountered... timing or bad timing in this case. The deletes in table B and table C were conflicting because none of the foreign keys had indexes on them making the constraint checks take longer leaving more time for deadlock to occur. After adding the following indexes the deadlocks were immediately resolved:

CREATE INDEX IX_D_TO_A_FK ON D
(
AId
)
CREATE INDEX IX_D_TO_C_FK ON D
(
CId
)