How to pass NULL instead of string format 'NULL' value using XML node in SQL server

I'm using XML node() method to extract XML data using SQLSserver 2008.

Now while extracting the XML data, one of the integer data type of XML field have a value like NULL and It is trying to insert as string format 'NULL' instead of NULL.

below is XML Node() query to extract data,

CREATE TABLE [dbo].[WO_Log](
[Flag] [int] NULL,
[FillingPO] [nvarchar](50) NULL,
[PackAtCAN1] [bit] NULL
) ON [PRIMARY]

declare @message XML

set @message='<ProductionOrder>
          <Header>
            <MessageID>00000005</MessageID>
            <PublishedDate>2013-05-30 09:30:10</PublishedDate>
          </Header>
          <Body>
            <ProductionOrderDetails>
              <Flag>NULL</Flag>
              <FillingPO>NULL</FillingPO>
              <PackAtCAN1>NULL</PackAtCAN1>
            </ProductionOrderDetails>
          </Body>
        </ProductionOrder>'


 INSERT INTO dbo.WO_Log(Flag,FillingPO,PackAtCAN1)
  SELECT p.value('Flag[1]','int')
         p.value('FillingPO[1]','NVARCHAR(50)'),
         p.value('PackAtCAN1[1]','BIT')
  FROM  @message.nodes('/ProductionOrder/Body/ProductionOrderDetails') x(p)

 Now when I'm execute the query i'm getting the error as below,

"Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'NULL' to data type int."

Solution

You can use cast in the xQuery for value(). It will return NULL if the cast fails.

SELECT p.value('Flag[1] cast as xs:int?','int'),
       p.value('FillingPO[1]','NVARCHAR(50)'),
       p.value('PackAtCAN1[1] cast as xs:boolean?','BIT')
FROM  @message.nodes('/ProductionOrder/Body/ProductionOrderDetails') x(p)

Type Casting Rules in XQuery

If you want to get NULL for the string value 'NULL' in a character node you can use isnull on the result from value().

SELECT p.value('Flag[1] cast as xs:int?','int'),
       nullif(p.value('FillingPO[1]','NVARCHAR(50)'), N'NULL'),
       p.value('PackAtCAN1[1] cast as xs:boolean?','BIT')
FROM  @message.nodes('/ProductionOrder/Body/ProductionOrderDetails') x(p)