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.
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…
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…
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.
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.