Working with null values in SQL XML

Question

Working with null values in SQL XML

R.P.Rozema

SSChampion

Points: 12317
More actions
October 25, 2010 at 8:52 pm

#388100

Comments posted to this topic are about the item Working with null values in SQL XML

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

terrance.steadman SSCrazy Points: 2097 More actions · Answer 1

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

sknox SSChampion Points: 12388 More actions · Answer 2

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.

deanroush SSC-Addicted Points: 474 More actions · Answer 3

I too am just starting with xml in my designs. Thank you for putting this out there!

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

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.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 5

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 6

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

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 7

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).

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

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

ismell SSC Enthusiast Points: 130 More actions · Answer 9

.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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 10

@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.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Working with null values in SQL XML

Cookies on SQLServerCentral