I've seen a lot of stuff out there regarding the SQL 2005 XML data type but most of it is just regurgitates the MSDN documentation. That's fine and all but what about practical uses of it? Well, I have a practical use sample. In building my persistent browser based game Perenthia I have a concept of a Place. A place is a virtual space in which objects are stored. For Perenthia the places represent the various rooms or tiles players move around on. The place or room has exits defined that allow the player to move from one place to the next. The exits are the typical directions; north, south, up, down, etc. In the database I have a Places table and a PlaceExits table. The Places table stores all the information regarding a place and the PlaceExits table stores the placeId along with a directionId and destinationId so I know what exits are available in any room and what rooms they lead to.
The simplified schema for the places would be:
In the stored procedure that retrieves the place information I use the following query snippet in the select clause:
SELECT
p.*,
(
SELECT
e.DirectionId AS "@directionId",
e.DestinationId AS "@destinationId"
FROM
dbo.PlaceExits e
WHERE
e.PlaceId = p.PlaceId
FOR XML PATH('exit'), ROOT('exits')
) AS ExitsXml
FROM dbo.Places p
This creates an XML fragment I can then parse in the application to fill a collection of Exits on the Place object.
When saving place information I pass XML generated from the Exits collection in a stored procedure like so:
CREATE PROCEDURE dbo.Places_SavePlace (@PlaceId int, @ExitsXml xml)
From within the save procedure I perform an update or insert of the place data and then execute the following sql to insert and update the exits for the current place:
-- Exits
-- Process the existing exits first
UPDATE
dbo.PlaceExits
SET
DestinationId = e.ex.value('(@destinationId)[1]', 'int')
FROM
@ExitsXml.nodes('/exits/exit') as e(ex)
WHERE
PlaceId = @PlaceId
AND DirectionId = e.ex.value('(@directionId)[1]', 'tinyint')
-- Process any new exits
INSERT INTO dbo.PlaceExits
(
ObjectId, DirectionId, DestinationId
)
SELECT
@PlaceId,
e.ex.value('(@directionId)[1]', 'tinyint'),
e.ex.value('(@destinationId)[1]', 'int')
FROM
@ExitsXml.nodes('/exits/exit') as e(ex)
WHERE
e.ex.value('(@directionId)[1]', 'tinyint') NOT IN
(
SELECT DirectionId FROM dbo.PlaceExits WHERE PlaceId = @PlaceId
)
This is working pretty well and keeps me from having to loop through the exits in the application and make multiple database calls.
11/14/2007