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

Question

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

Mikael Eriksson · Accepted Answer · 2014-10-30 13:12:14Z

1

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)

edited Oct 30, 2014 at 13:12

answered Oct 30, 2014 at 13:04

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related