SQL 2005 XML Data Type, Stored Procedures and Lists

by Cameron Albert 14. November 2007 17:41

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:

 Places Tables

 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. 

PBBG Engine Core Object Structure

by Cameron Albert 6. November 2007 13:20

The core of my PBBG Engine consists of a Place and an Object. A Place is a spacial definition and can represent a room, world, universe, etc. An Object is anything that can reside within a place, meaning all people, monsters and items are Objects. The Places and Objects are defined using meta data so that properties of an Object are the meta data of the object. This meta data is stored in a seperate table from the object and retrieved as XML when an object is queried. Places work in much the same way with a few pre-defined fields such as the name and x, y, z location of the place. Going this route will allow me to have different objects defined in different games without having null database fields that are not used in all games.

Another example would be a Sword. In Perenthia a Sword dervies from Weapon which dervies from Thing which implements the Object interface required by the PBBG Engine in order to persist the properties of the Sword in the database. I can then create instances of the Sword class when Swords are crafted or purchased. Each level of inheritence can implement properties that will persist for each Sword instance created.

Sword : Weapon : Thing : IObject 

So far it seems to be working pretty well and with the XML features of SQL 2005 the querying of the data is very fast. I am hoping to get Perenthia ported over to this new framework soon as it will provide a more flexible system and allow me to manage game content a little easier. 

Passing Lists to SQL Server 2005 using XML

by Cameron Albert 31. October 2007 14:44
Jon Galloway wrote up a nice article on passing lists in a stored procedure using XML and SQL 2005's ability to query XML. For anyone who ever wanted to pass an array to a stored procedure this a good read. :)

Tags: , , ,

ASP.NET Development | General

Powered by BlogEngine.NET 1.5.0.7
Modified Theme by Mads Kristensen

About the Author

CameronAlbert.com I am Senior Software Development Consultant specializing in Silverlight, WPF and the Microsoft .NET Framework. 

I have released an iPhone game called the Adventures of Puppyman that was built using ExEn and am currently working on a WP7 and iPhone version of Perenthia soon to be released.

View Cameron Albert's profile on LinkedIn
See how we're connected

Follow cameronalbert on Twitter

 

Recommended Books

Silverlight 4 Business Application Development - Beginner's Guide:

http://www.packtpub.com/microsoft-silverlight-4-business-application-development-beginners-guide/book

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide