XML Modification

As a followup to this, I needed to shred the xml (passed into the process) to the values denoted.

See this sample. Note the error that’s commented out from the first select statement.

--Params we need
DECLARE @XMLBlob XML
DECLARE @FirstInt INT
DECLARE @SecondInt INT

--XML to play with
SET @XMLBlob =
'<Parameters>
    <Parameter Name="FirstInt">NULL</Parameter>
    <Parameter Name="SecondInt">2</Parameter>
</Parameters>'


--This will error: Conversion failed when converting the nvarchar value 'NULL' to data type int.
SELECT @FirstInt = @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
--This will work!
SELECT @SecondInt = @XMLBlob.VALUE('(//Parameter[@Name="SecondInt"])[1]', 'INT' )

So, I needed to try to solve this, so I came up with the following T-SQL at first…

SELECT @FirstInt =
    CASE
        WHEN @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'VARCHAR(100)' ) = 'NULL' THEN NULL
        ELSE @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
    END

The above code works, but is far from ideal. Especially considering that this is a simplified example; the real one has many, many more params. After completing the case statement, I took a step back to look at what I had done and laughed at myself.

Here’s the real solution…

SET @XMLBlob.MODIFY( 'delete (//Parameter[text()="NULL"])' )

All the statement does is look for matches on //Parameter where the text is the string “null” as compared to the value null. Then, it deletes these rows.

Now, here is the whole thing from beginning to end.

--Params we need
DECLARE @XMLBlob XML
DECLARE @FirstInt INT
DECLARE @SecondInt INT

--XML to play with
SET @XMLBlob =
'<Parameters>
    <Parameter Name="FirstInt">NULL</Parameter>
    <Parameter Name="SecondInt">2</Parameter>
</Parameters>'


SET @XMLBlob.MODIFY( 'delete (//Parameter[text()="NULL"])' )

--This works now!
SELECT @FirstInt = @XMLBlob.VALUE('(//Parameter[@Name="FirstInt"])[1]', 'INT' )
--This will still work!
SELECT @SecondInt = @XMLBlob.VALUE('(//Parameter[@Name="SecondInt"])[1]', 'INT' )

This won’t work in all applications – for example ones where you needed to know if something was passed in – but it works when all you’re doing is checking for the existence of a value.

This entry was posted in SQL Server Development and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *