Something’s that easy to forget due to the case sensitive nature of the syntax.
XML that looks like this…
<Parameters>
<Parameter Name="FirstInt">NULL</Parameter>
<Parameter Name="SecondInt">2</Parameter>
</Parameters>
<Parameter Name="FirstInt">NULL</Parameter>
<Parameter Name="SecondInt">2</Parameter>
</Parameters>
and has this t-sql run against it…
SELECT
T.c.VALUE( '@Name', 'VARCHAR(36)' ) AS Name
, T.c.VALUE( '.', 'VARCHAR(36)' ) AS VALUE
FROM @ExecutionParameters.nodes('//Parameter') AS T(c)
WHERE T.c.VALUE( '.', 'VARCHAR(36)' ) = 'NULL'
T.c.VALUE( '@Name', 'VARCHAR(36)' ) AS Name
, T.c.VALUE( '.', 'VARCHAR(36)' ) AS VALUE
FROM @ExecutionParameters.nodes('//Parameter') AS T(c)
WHERE T.c.VALUE( '.', 'VARCHAR(36)' ) = 'NULL'
returns a rowset that looks like…
Name Value
FirstInt NULL
SecondInt 2
FirstInt NULL
SecondInt 2