SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with null values in SQL XML


Working with null values in SQL XML

Author
Message
R.P.Rozema
R.P.Rozema
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1439 Visits: 1701
Comments posted to this topic are about the item Working with null values in SQL XML



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


If you don't have time to do it right, when will you have time to do it over?
terrance.steadman
terrance.steadman
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 538
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. :-D

Have a good day.

Terry Steadman
sknox
sknox
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3017 Visits: 2850
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
deanroush
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 265
I too am just starting with xml in my designs. Thank you for putting this out there!
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4717 Visits: 1619
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
Oleg Netchaev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1863 Visits: 1815
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
R.P.Rozema
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1439 Visits: 1701
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
antonio.collins
antonio.collins
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1072 Visits: 921
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
Hardy21
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1858 Visits: 1399
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
ismell
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 61
.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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search