retrieving NULL values using OPENXML

• sheila.filteau

  Old Hand

  Points: 355

  More actions

  April 7, 2010 at 12:50 pm

  #386659

  I am having trouble retrieving NULL values using OPENXML. For some reason the statement returns 0 for int fields and blanks for nvarchar fields. Any ideas on how I can get it to return NULL?

  I have already tried using ISNULL but it does not recognise the value as NULL.

  I'm using SQL Server 2008 R2

  Here is sample code:

  ------------

  declare @parSampleXML xml

  ,@XmlDocHandle int

  set @parSampleXML = '<sampledoc>

  <Field1 />

  <Field2>1</Field2>

  <Field3>Test data</Field3>

  <Field4></Field4>

  </sampledoc>'

  EXEC sp_xml_preparedocument @XmlDocHandle OUTPUT, @parSampleXML,

  '<SampleDoc xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

  select @parSampleXML

  SELECT Field1

  , Field2

  , Field3

  , Field4

  FROM OPENXML (@XmlDocHandle,'/sampledoc', 2) --2 = element-centric mapping

  WITH (Field1int ,

  Field2int ,

  Field3nvarchar(28),

  Field4nvarchar(28)

  )

  Yields this result:

  -----------------------------------------------------------------------------------------

  <sampledoc><Field1 /><Field2>1</Field2><Field3>Test data</Field3><Field4 /></sampledoc>

  Field1 Field2 Field3 Field4

  --------- ----------- ---------------------------- ----------------------------

  0 1 Test data

  (1 row(s) affected)

  But this is what I want:

  Field1 Field2 Field3 Field4

  ---------- ----------- ---------------------------- ----------------------------

  NULL 1 Test data NULL

  (1 row(s) affected)

  Thanks for your help.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 7, 2010 at 12:56 pm

  #1147694

  You could use NullIf. Would that do what you need?

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• sheila.filteau

  Old Hand

  Points: 355

  More actions

  April 7, 2010 at 1:34 pm

  #1147713

  NULLIF works if I test for the empty string. That is not as nice as I would like but it meets the need. Thanks.

  SELECT NULLIF(Field1, '') as Field1

  , NULLIF(Field2, '') as Field2

  , NULLIF(Field3, '') as Field3

  , NULLIF(Field4, '') as Field4

  FROM OPENXML (@XmlDocHandle,'/sampledoc', 2) --2 = element-centric mapping

  WITH (Field1int ,

  Field2int ,

  Field3nvarchar(28),

  Field4nvarchar(28)

  )