OPENXML, Xsi:nil and Decimals

Question

I have some XML containing xsi:nil="true" for certain string and numeric elements. Here's an example:

declare @data xml

set @data = '<?xml version="1.0" encoding="utf-8"?>
             <collection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
              <item>
               <stringprop1>foo</stringprop1>
               <stringprop2 xsi:nil="true" />
               <decimalprop3 xsi:nil="true" />
              </item>
             </collection>'

I want to query that XML in SQL Server 2008 R2. I'm using OPENXML but it's not playing nicely with decimal types. Here's the code I'd like to write:

declare @doc int
exec sp_xml_preparedocument @doc OUTPUT, @data;

select 
  stringprop1,
  stringprop2,
  decimalprop3  
from openxml(@doc, '/collection/item', 2)
with
(
  stringprop1 nvarchar(50)
  ,stringprop2 nvarchar(50)
  ,decimalprop3 decimal(18, 5)
)

exec sp_xml_removedocument @doc;

This complains about converting nvarchar to decimal. After some hacking around, I arrived at this:

exec sp_xml_preparedocument @doc OUTPUT, @data;

select 
  nullif(stringprop1, '') as stringprop1,
  nullif(stringprop2, '') as stringprop2,
  convert(decimal(18, 5), nullif(decimalprop3, '')) as decimalprop3
from openxml(@doc, '/collection/item', 2)
with
(
  stringprop1 nvarchar(50)
  ,stringprop2 nvarchar(50)
  ,decimalprop3 nvarchar(50)
)

exec sp_xml_removedocument @doc;

Which is fine, I guess. But is there any way to tell OPENXML that xsi:nil means NULL and that that's ok for decimals as well as strings?

Answer 1

xsi:nil is an XML Schema feature and OpenXML was designed before it's existance and does not support xsi:nil. Since you use SQL Server 2008, one way to make this to work is to:

1. constrain the XML with the appropriate XML Schema that validates the data and will recognize the xsi:nil and map it to the empty value.

2. use the nodes() and value() methods to extract the data.

Best regards Michael