title: How to import import blank XML tags into a Number column of a table using a stored procedure
body: <p>I have created a stored procedure which converts a XML file into a SQL Server data table. This works well when there are values in each tag for number formatted columns but does not work if the tag is empty. Is there a way to convert blank to null or a way to ignore blanks within the stored procedure? Here is a simplified version of the procedure.</p>
  
  <pre><code>SET ANSI_NULLS ON 
  GO 
  
  SET QUOTED_IDENTIFIER ON 
  GO 
  
  CREATE PROCEDURE [dbo].[SHPReportDataXML] 
      @XML XML
  AS 
      INSERT dbo.SHPReportData ([Weight], [WeightUQ]) 
          SELECT
              [Weight] = SHPReportData.value('(RawDataItem/Weight)[1]', 'DECIMAL(10, 2)'),
              [WeightUQ] = SHPReportData.value('(RawDataItem/UQ)[1]', 'VARCHAR(2)')
          FROM
              @XML.nodes('RawData') c (SHPReportData);
  </code></pre>