• andrew.sims (7/22/2009)


    Nice article.

    Would it also be possible to look at the same processes where the XML data is arranged like...

    Jacob

    NY

    SQL Server

    Steve

    SQL Server

    Bob

    CA

    Which include blank elements.

    Have already been caught out with data like this needing insert/modify depending if a value exists

    Thanks

    Andy

    Hi Andy,

    It is possible by modifying the Xquery expression slightly, as given in the following example

    DECLARE @x XML

    SELECT @x = '

    Jacob

    NY

    SQL Server

    Steve

    NY

    ASP.NET

    '

    DECLARE @Team VARCHAR(20)

    SELECT @Team = 'SQL Server'

    SET @x.modify('

    delete (employees/employee[team=sql:variable("@Team")]/city)

    ')

    SELECT @x

    /*

    Jacob

    SQL Server

    Steve

    NY

    ASP.NET

    */

    .