Systems Engineering and RDBMS

Null values in XML

Posted by decipherinfosys on March 31, 2009

We have blogged before about the usage of XSINIL to be able to generate elements for null values.  Let’s take this up with an example and then we will discuss the issue that we faced recently at a client site.

SET NOCOUNT ON
/* Prepare sample data*/
DECLARE @table TABLE (dept_id int, emp_name varchar(30), hire_date datetime)

INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (10, 'Jack', '12/12/2003')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (10, 'John', '03/11/2007')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (10, 'Beth', '05/11/2008')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (20, 'Mary', '01/03/1999')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (20, 'Allen', '05/11/2000')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (20, 'Diana', '09/09/2001')
INSERT INTO @table (dept_id, emp_name, hire_date) VALUES (20, 'Ramesh', Null)

select dept_id, emp_name, hire_date from @table as Dept_Emp for xml auto, elements, Root('Dept');
<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
</Dept_Emp>
</Dept>

And in the generated data set, you will see that for the last record, there is no element for HIRE_DATE since the value is null. Now, let’s add XSINIL to the elements directive and that will force the element to be created even for the Null value:

select dept_id, emp_name, hire_date from @table as Dept_Emp for xml auto, elements XSINIL, Root('Dept');

<Dept xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date xsi:nil="true" />
</Dept_Emp>
</Dept>

You will see that for the hire_date, it has now added the element with the xsi:nil=”true” attribute.  That will be the case for every Null column value that gets returned by the query.  Now, all this is fine when we are working with SQL Server or are generating data via SQL Server and consuming it that way. So, using the elements (and xsinil) directive in auto, raw or path mode queries, we can tackle the issue of null values.  We can also do so by using the elementxsinil column mode in the explicit mode code however, with SQL Server 2005, the path mode offers a much more flexible and easier way to generate XML than to use the explicit mode though there are certain things like the CDATA section that cannot be done in other modes.  More on that later…

However, in our recent project, we were getting the XML via a third party application which was generating the data set from a main frame application and for Null values, they would simply leave the element empty and provide it as a string rather than a datetime, so in the above example, we were getting:

<hire_date></hire_date>

This created an issue because this was getting consumed by the system and stored as the default date since this was treated like an empty string instead of a Null value by the code and as we have discussed in the past, an empty string is not the same as Null – you can read more on that here.

So, how can we work around this issue since the source application cannot be changed…we will need to handle this in the SQL code that reads the XML:

declare @x xml
select @x =
'<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date></hire_date>
</Dept_Emp>
</Dept>'

select
x.i.value('dept_id[1]', 'int') as Dept_ID
,x.i.value('emp_name[1]', 'nvarchar(50)') as Emp_Name
,x.i.value('hire_date[1]', 'datetime') as hire_date
from @x.nodes('//Dept_Emp') as x(i)

This execution will translate the empty string for the datetime value to the default value of: “1900-01-01 00:00:00.000”

Dept_ID     Emp_Name                                           hire_date
----------- -------------------------------------------------- -----------------------
10          Jack                                               2003-12-12 00:00:00.000
10          John                                               2007-03-11 00:00:00.000
10          Beth                                               2008-05-11 00:00:00.000
20          Mary                                               1999-01-03 00:00:00.000
20          Allen                                              2000-05-11 00:00:00.000
20          Diana                                              2001-09-09 00:00:00.000
20          Ramesh                                             1900-01-01 00:00:00.000

One way to solve this is to just use the NULLIF() function:

select
x.i.value('dept_id[1]', 'int') as Dept_ID
,x.i.value('emp_name[1]', 'nvarchar(50)') as Emp_Name
,nullif(x.i.value('hire_date[1]', 'datetime'), '1900-01-01 00:00:00.000') as hire_date
from @x.nodes('//Dept_Emp') as x(i)

This will compare the input value with the default value and translate it into Null and then we can process the data.

Resources:

  • Empty String vs Null – here.
  • Nodes Method – here.

2 Responses to “Null values in XML”

  1. saravanan said

    Dear friends. i want to get the null values in the column in the above table in oracle 10g.
    what is the syntax?
    eg:select dept_id, emp_name, hire_date from @table as Dept_Emp for xml auto, elements XSINIL, Root(‘Dept’);(this is not an oracle statement)

    if anyone knows please let me know
    thanks
    saravanan
    saravanan_at_pentasoft_dot_com_dot_my

  2. […] also about the default date of 1900-01-01 which we have covered in our blog posts before – here.  Also, note that an empty string is different than NULL value – the default value for an […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: