Monday, 16 January 2012

Excluding nodes from XML data before returning from SQL Server

This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL.

Example setup

CREATE TABLE Example
(
ID INTEGER IDENTITY(1,1) PRIMARY KEY,
XmlField XML
)

INSERT Example (XmlField) VALUES ('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>')
So if you want to return the XML minus the ChildB node, how do you do it?

Modify it

Literally, using the modify method that is supported on the XML data type. To quote MSDN, modify:
Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.
That last statement, to me at least, seems slightly open for misinterpretation as strictly speaking it isn't only valid in an UPDATE statement - you can use it as part of a standalone SET statement on an XML variable (though yes, to UPDATE that variable).

So, while you can't do something like this:
SELECT XmlField.modify('delete /Root/ChildB')
FROM Example
WHERE ID = 1
as it results in the following error:
Msg 8137, Level 16, State 1, Line 1
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.
you can instead do this:
DECLARE @xml XML
SELECT @xml = XmlField
FROM Example
WHERE ID = 1

-- Delete the node from the XML in the variable
SET @xml.modify('delete /Root/ChildB')

SELECT @xml
That does the trick.

Dealing with multiple rows

If you wanted to do something like this from the database side when returning multiple rows then you'd need to either:
  1. create a scalar UDF to perform the removal. e.g.
    CREATE FUNCTION dbo.RemoveNode(@xml XML)
     RETURNS XML
    AS
    BEGIN
     SET @xml.modify('delete /Root/ChildB')
     RETURN @xml
    END
    GO
    
    SELECT dbo.RemoveNode(XmlField) AS XmlField
    FROM Example
    
    Note the parameter to the modify method must be a literal string, so you can't parameterise the UDF to make it a bit more generic by using that parameter in the call to modify().

  2. SELECT the xml values into a temp table/table variable, do an UPDATE like this:
    UPDATE #Temp
    SET XmlField.modify('delete /Root/ChildB')
    
    and then SELECT from the temp table/table variable.

  3. Use SQLCLR
Of course, the typical question should be asked - do you need to do this on the database side, or can you return the XML as-is from SQL Server and modify it accordingly in your calling code? In the original case acting on a single XML value, it's OK but when you start looking into scalar UDF's etc for multi-row requirements then you should be careful to check performance.

Monday, 2 January 2012

Round up of 2011

Another year is done and dusted so it's that time again - time to reflect on the past year.

Quick Timeline
  • March: Mongo UK - London
    A chance to attend my first NOSQL conference, having recently entered in the world of MongoDB for some projects. Picked up some good pointers and food-for-thought.
    Just some words: NOSQL, hard seats, gonna-need-a-bigger-venue, enlightening.
  • April: SQLBits 8 - Beside The Seaside - Brighton
    Highlight of the year for me, conference-wise (was unable to make SQLBits 9 - Liverpool). Fantastic venue, truly awesome speakers/technical content and a really great crowd of people - a true community. Out of all conferences I've gone to over the years, SQLBits never fails to stand out and this one took things to a whole new level.
    Just some words: Crappy code, community, awesome, invaluable, bean bags.
  • April: Microsoft Community Contributor Award
    I really enjoy chipping in on sites like StackOverflow and the MSDN forums, and this was a nice (humbling) surprise when I got the email to say I'd received this acknowledgement (blog).
    Just some words: Surprise
  • July: Job Change
    After a great 4 and a half years, it was time to move on to a new role. Took the opportunity to work with fellow SQLSoton attendee Matt Whitfield (twitter) - who by the way, has some great (FREE) SQL Server tools which can be found here.
    Just some words: New challenge, acquisition, end of an era.
  • September: Mongo UK - London
    A chance to brush up on the latest in the world of MongoDB after a few months away from it.
    Just some words: NOSQL, comfy seats, information gems, lego man USB stick.
  • Monthly: SQLSoton User Group
    A really good year of UserGroup meets down here in Southampton every month. Thanks to Mark Pryce-Maher (twitter) for his time and effort, and all the speakers who have taken the time to give a talk. From the first meeting just over a year a go - a small group, in a tiny side room, with no projector - to now - a larger crowd, in a bigger hall, with a projector - it's obvious SQLSoton is providing a valuable SQL Server community service to the Southampton/South Coast area. Check out all the UK SQL Server UserGroups over at SQLServerFAQ.
    Just some words: Community, pizza, swag, network.

Top 3 Blog Posts By Views
  1. High Performance Bulk Loading to SQL Server Using SqlBulkCopy
  2. SQL Server 2008 - Table Valued Parameters vs XML vs CSV
  3. Getting Started With Cassandra and .NET
  4. Ordnance Survey Post Code Geographic Data Load to SQL Server Using .NET*
* OK, so that's the top 4, not the top 3. But that's because I suspect #3 is actually skewed by people looking for "an entirely different kind of site"...I know I had to be careful which links to click when googling for Cassandra (as in the NOSQL database, obviously) and .NET!

Open Source Projects
I started on a few little side projects, which I stuck up on to GitHub for the world to see and (hopefully) use. First came a .NET app to load some of the freely available geographical datasets from Ordnance Survey into SQL Server (GitHub) which relates to blog post #4 above. Next came a MongoDB-backed ASP.NET session state store (GitHub). It was a lot of fun working on these, even though they are pretty trivial things, and was great to actually get some nice feedback from people who found and started using them.

Bottom Line
2011 was a good year. A lot of learning and new challenges meant I was kept on my toes. Looking back though, there are definitely things I'm not pleased about. I dropped the blogging ball in the second half of the year - activity pretty much flatlined for various reasons. So I'm disappointed about that. To quote a tweet by John Sansom (twitter)...
blogging is like going to the gym. Once you fall out of a routine it's tougher to get back into your rhythm
True that. Looking forward to 2012!