OPENXML, Xsi:nil and Decimals

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_开发者_JS百科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?

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

This should do:

Declare @doc int
exec sp_xml_preparedocument @doc OUTPUT, @data, '< row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />'

select  
    stringprop1, 
    stringprop2, 
    decimalprop3 
from openxml(@doc, '/collection/item', 2) 
with 
( 
    stringprop1 nvarchar(50), 
    stringprop2 nvarchar(50) 'stringprop2[not(@xsi:nil = "true")]', 
    decimalprop3 nvarchar(50) 'decimalprop3[not(@xsi:nil = "true")]' 
) 

exec sp_xml_removedocument @doc; 

Necromancing.
This is what works for me:

-- How to create the XML 
/*
DECLARE @xml XML 
SET @xml = ( SELECT (SELECT * FROM T_Benutzer FOR XML PATH('row'), ROOT('table'),  ELEMENTS xsinil) AS outerXml )

-- SELECT @xml 
*/


DECLARE @xml xml 
SET @xml = '<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <PLK_UID>7CA68E6E-E998-FF92-BE70-126064765EAB</PLK_UID>
    <PLK_Status>1</PLK_Status>
    <BLA_UID>00000000-0000-0000-0000-000000000000</BLA_UID>
  </row>
  <row>
    <PLK_UID>C8A19BB1-6B45-67A6-0417-1F455EE8D2E1</PLK_UID>
    <PLK_Status>1</PLK_Status>
    <BLA_UID xsi:nil="true" />
  </row>
  <row>
    <PLK_UID>C8A19BB1-6B45-67A6-0417-1F455EE8D2E2</PLK_UID>
    <PLK_Status xsi:nil="true" />
    <BLA_UID xsi:nil="true" />
  </row>
  <row>
    <PLK_UID>C8A19BB1-0000-67A6-0417-1F455EE8D2E3</PLK_UID>
    <PLK_Status xsi:nil="true" />
    <BLA_UID>C8A19BB1-1111-67A6-0417-1F455EE8D2E1</BLA_UID>
  </row>
  <row>
    <PLK_UID>C8A19BB1-0001-67A6-0417-1F455EE8D2E4</PLK_UID>
    <PLK_Status>666</PLK_Status>
    <BLA_UID>C8A19BB1-1112-67A6-0417-1F455EE8D2E1</BLA_UID>
  </row>
</table>'


DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @XML

SELECT 
     PLK_UID 
    ,PLK_Status 
    ,BLA_UID
FROM OPENXML(@handle, '/table/row', 2) WITH 
(
     PLK_UID uniqueidentifier 'PLK_UID[not(@*[local-name()="nil" and . ="true"])]' 
    ,PLK_Status int  'PLK_Status[not(@*[local-name()="nil" and . ="true"])]' 
    ,BLA_UID uniqueidentifier  'BLA_UID[not(@*[local-name()="nil" and . ="true"])]' 
)  

WHERE (1=1) -- AND

EXEC sp_xml_removedocument @handle 

How to create the field-list:

SELECT 
      CASE WHEN ORDINAL_POSITION = 1 THEN N'     ' ELSE N'    ,' END 
    + N'"' + COLUMN_NAME 
    + N'" '  
    + 
    CASE 
        WHEN DATA_TYPE = 'nvarchar' THEN N'national character varying' 
        WHEN DATA_TYPE = 'varchar' THEN N'character varying' 
        ELSE DATA_TYPE 
    END 
    + 
    CASE 
        WHEN DATA_TYPE IN ('char', 'nchar', 'binary') THEN 
                  N'(' 
                + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(36)) 
                + N')' 
        WHEN DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') THEN 
                N'(' + 
                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(36)) END 
                + N')' 
        WHEN DATA_TYPE IN ('datetimeoffset', 'datetime2', 'time', 'smalldatetime') THEN 
                  N'(' 
                + CAST(DATETIME_PRECISION AS nvarchar(36)) 
                + N')' 
        WHEN DATA_TYPE IN ('decimal', 'numeric') THEN 
                  N'(' 
                + CAST(NUMERIC_PRECISION AS nvarchar(36)) 
                + N',' 
                + CAST(NUMERIC_SCALE AS nvarchar(36)) 
                + N')' 

        ELSE N'' -- N'(default)' 
    END 
    + ' ''' + COLUMN_NAME + '[not(@*[local-name()="nil" and . ="true"])]''' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dbo' 
AND TABLE_NAME = 'T_Benutzer' 
ORDER BY ORDINAL_POSITION