Erik Porter's Blog

Life and Development at Microsoft and Other Technology Discussions

News

    Taking Advantage of Xml Data Type in Sql Server 2005

    There are a ton of interesting things you can do with the new Xml data type in Sql Server 2005.  The first quick thing that came to mind was to pass in an Xml document into a stored procedure to have many rows deleted at one time in a batch without having to use a loop with a transaction outside of Sql Server in .NET code.  This could be done before in Sql Server 2000 by passing in an Xml document as a nvarchar then using OPENXML to parse out the values you need.  I find the new way to be a bit cleaner.  Here's a little t-sql script you could easily turn into a stored procedure to do multiple deletes at once.

    DECLARE @IDList xml

    SET @IDList = '<IDList><Item ID="1" /><Item ID="2" /><Item ID="3" /></IDList>'

    DELETE FROM
        
    MyTable
    WHERE
        
    MyTableID IN
        
    (
             
    SELECT
                  
    IDList.Item.value('@ID', 'int') AS ID
             
    FROM
                  
    @IDList.nodes('/IDList/Item') IDList (Item)
        
    )

    In short, it's just saying, give me the nodes that match this XQuery, then gives the results an alias of IDList, saying that it has one field in it called Item and then calling the value method on it asking for the attribute called ID and making sure it's returned as an int.  It then returns that result to the calling delete statement so any row with those IDs in your table will be deleted.  Pretty simple and neat, but the xml data type starts to get really neat when you start storing metadata about each row.  You can use the documented methods to pull out different fields in your result set from the xml field in your table so they act just like any other field inside of Sql Server.

    I am by no means an expert at Sql Server 2005 so if there's a better way to do what I've demonstrated, please post a comment.  The documentation seems to be a big lacking right now.

    Comments

    Sundaresan said:

    Is there any size limits & performance issues using XML data type in large volume operations (Reads/Writes)
    # November 28, 2005 2:13 PM

    Erik Porter said:

    I honestly have no idea. You should try some very large documents and post your findings.
    # November 28, 2005 2:15 PM

    Kent Tegels said:

    Erik, this looks very much like the solution I would have crufted up. Good job!
    # November 29, 2005 3:43 AM

    Erik Porter said:

    Thanks, Kent! Means I must've done something right coming from you. :)
    # November 29, 2005 2:09 PM

    Jason Mauss said:

    This is actually pretty cool. I've often had to use the IN() statement or kluge together something in order to peform more than one insert/delete on a single round-trip to the DB Server. Neat.
    # November 29, 2005 7:50 PM

    Erik Porter said:

    Yup, I should've mentioned it's great for inserts too!
    # November 29, 2005 7:53 PM

    sbyard said:

    Just what I wanted. Because the ASP.NET membership system allows multiple roles per user, I needed a common function that I could rely on in lots of Stored Procedures.

    ALTER FUNCTION [dbo].[RoleTableFromXml]
    (
    @RoleXml xml
    )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT
    LOWER(UserRoles.UserRole.value('@UserRoleName', 'varchar(256)')) AS RoleName
    FROM
    @RoleXml.nodes('/UserRoles/UserRole') UserRoles (UserRole)
    )
    # December 22, 2005 3:01 PM

    Vishal said:

    Meghna, the xml datatype will help mainly in validating your xml (the stored procedure parameter) when bound to an xml schema collection at the SQL Server database level.

    # August 21, 2007 11:06 AM

    Gaurav said:

    What about following kind of XML, I don't think its giving you the same output as you are showing here.

    <DSID ID="123">

     <Field name="A">

       <F_Data>Value1</F_Data>

       <F_Ops>IN</F_Ops>

     </Field>

    <DSID>

    # March 25, 2008 5:52 PM