October 25, 2010 at 8:52 pm
Comments posted to this topic are about the item Working with null values in SQL XML
October 26, 2010 at 6:36 am
Greetings Richard,
That was very informative and helpful. I am starting to work with some XML now so thank you for showing how to solve the problem just before I hit it. 😀
Have a good day.
Terry Steadman
October 26, 2010 at 7:20 am
Of course, if you're storing your value in an attribute, NULL is represented by the absence of the attribute in the record element, and SQLXML deals with that fine.
Except for large data values, I prefer to store row values in attributes, as it better preserves the normality of the data and keeps the XML that much more compact.
October 26, 2010 at 7:27 am
I too am just starting with xml in my designs. Thank you for putting this out there!
October 26, 2010 at 8:02 am
XML always remained mystery to me. Never understood the concept completely. May because I never worked on a project which had mandatory XML programming.
And just last week got a project where I had to move XML data from SQL Server to Oracle 10g, as its a PITA.
Thanks for the article, Rick.
SQL DBA.
October 26, 2010 at 9:29 am
sknox (10/26/2010)
Of course, if you're storing your value in an attribute, NULL is represented by the absence of the attribute in the record element, and SQLXML deals with that fine.
Absolutely correct, and I would like to add my 2 cents about the inner text treatment. Inner text is treated in exact same fashion. If you would like the statement to return null values, just don't provide the node at all and SQL Server will deal with it just fine as well. For example, the xml sample below has 2 "records". First record does not have the node named Gender and the second record does not have the ID attribute. This means that the first record will have null for Gender column and second - null for ID column. I believe that this is the simplest way to deal with nulls, specifically because the out of the box minOccurences of any node or attribute is zero anyway.
declare @xml xml;
set @xml = '
<rows>
<r ID="1">
<LastName>Clown</LastName>
<FirstName>Bozo</FirstName>
<MiscInfo>Bozo is a clown at Wringley</MiscInfo>
</r>
<r>
<LastName>Netchaev</LastName>
<FirstName>Oleg</FirstName>
<Gender>Male</Gender>
<MiscInfo>Oleg''s info goes here</MiscInfo>
</r>
</rows>
';
select
item.value('@ID', 'int') ID,
item.value('LastName[1]', 'varchar(10)') LastName,
item.value('FirstName[1]', 'varchar(10)') FirstName,
item.value('Gender[1]', 'varchar(10)') Gender,
item.value('MiscInfo[1]', 'varchar(35)') MiscInfo
from @xml.nodes('//rows/r') R(item);
The above returns expected output:
ID LastName FirstName Gender MiscInfo
----------- ---------- ---------- ---------- -----------------------------------
1 Clown Bozo NULL Bozo is a clown at Wringley
NULL Netchaev Oleg Male Oleg's info goes here
Very good article, thank you Richard. It is great to learn about different methods to achieve the same result.
Oleg
October 26, 2010 at 2:40 pm
sknox (10/26/2010)
Of course, if you're storing your value in an attribute, NULL is represented by the absence of the attribute in the record element, and SQLXML deals with that fine.Except for large data values, I prefer to store row values in attributes, as it better preserves the normality of the data and keeps the XML that much more compact.
Querying for an attribute that is not available in the document will indeed make value() return NULL, just like when an element is not specified. However, not specifying the attribute may have different interpretation than specifying an element with a nil attribute, even though in both situations the value() function will return NULL.
For example imagine a procedure that takes an xml document as a parameter. This procedure will update some record(s) using the values from the xml document. In this situation the choice between storing the values in attributes or elements does make a difference:
Storing the values in attributes means you have to choose what will happen if no attribute for a column is specified: will you leave the current value in that column unchanged or will you assign the column a null value? You will have to choose either option, and by doing so the other option is not possible any more. However, if you define your xml so that the values are stored in elements, you can explicitly set the column to null by specifying an xsi:nil="true" attribute on that column's element to make the procedure put a NULL value in the column or you can leave the column unchanged by not including the element for that column in the xml.
So, yes: using attributes instead of elements to store your values results in more compact xml documents and it may even result in slightly better performance processing the xml documents. But you have to be absolutely sure you will never need the added semantic options that using elements offers. If you need to switch from attribute oriented into element oriented after the solution has been taken into production, you may be in serious trouble.
edit: elaborated on the consequences of choosing attributes
October 26, 2010 at 6:12 pm
Querying for an attribute that is not available in the document will indeed make value() return NULL, just like when an element is not specified. However, not specifying the attribute may have different interpretation than specifying an element with a nil attribute, even though in both situations the value() function will return NULL.
using xpath's not() function makes null handling really simple, especially in xsl transforms. //Employee[not(@certification)] will easily find all Employee elements with no certification attribute (i.e.: Employee.certification is null).
October 27, 2010 at 6:38 am
Really nice article.
We are using XML but we were not facing any issue because we are not passing date type value. Thanks for pointing the issue.
Thanks
October 27, 2010 at 8:30 am
.value() will actually return a NULL value if you have Typed XML. If you create an XML schema with nillable=true then .value will actually handle all the hard work.
i.e)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:element name="BusinessFunction">
<xs:complexType>
<xs:sequence>
<xs:element name="Business_Function_Id" type="xs:int" maxOccurs="1" minOccurs="1" />
<xs:element name="Parent_Business_Function_Id" type="xs:int" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="Name" type="xs:string" maxOccurs="1" minOccurs="0" />
<xs:element name="Description" type="xs:string" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="Begin_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" />
<xs:element name="End_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="Update_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" />
<xs:element name="Delete_Dt" type="xs:dateTime" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="TimeStamp" type="xs:base64Binary" maxOccurs="1" minOccurs="0" nillable="true" />
</xs:sequence>
</xs:complexType>
</xs:element>
As you can see my Delete_Dt and End_Dt columns have a nullable="true" attribute.
So when you actually call value('Delete_Dt', 'dateTime') it will return a NULL value instead of the default.
i.e)
declare @xml xml (TrackingXML)
SET @xml = '<BusinessFunction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Business_Function_Id>0</Business_Function_Id><Delete_Dt xsi:nil="true" /></BusinessFunction>'
SELECT x.[BusinessFunction].value('Delete_Dt', 'dateTime')
FROM @xml.nodes('/BusinessFunction') x([BusinessFunction])
Give it a shot,
Raul
November 2, 2010 at 12:09 am
@ismel: Yes, you are right. Typed xml will return you a correct null value because the parser then 'knows' about the element being nillable. In fact it then uses the exact same xsi:nil="true" attribute to indicate the null values. I indeed forgot to mention typed xml and it's different behaviour in the article. Personally I still find it hard to type the xml's in my projects: even though typed xml can be very useful, the xml collections impose some limitations that I can often not live with. That's probably why I forgot about them in the article;). Thank you for the valuable addition.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply