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
*/
.