Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Working with null values in SQL XML Expand / Collapse
Author
Message
Posted Monday, October 25, 2010 8:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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?
Post #1010514
Posted Tuesday, October 26, 2010 6:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 9:21 AM
Points: 176, Visits: 463
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
Post #1010729
Posted Tuesday, October 26, 2010 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:39 PM
Points: 1,382, Visits: 1,752
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.
Post #1010775
Posted Tuesday, October 26, 2010 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:18 AM
Points: 26, Visits: 265
I too am just starting with xml in my designs. Thank you for putting this out there!
Post #1010778
Posted Tuesday, October 26, 2010 8:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #1010825
Posted Tuesday, October 26, 2010 9:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #1010924
Posted Tuesday, October 26, 2010 2:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:32 AM
Points: 412, Visits: 1,412
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?
Post #1011145
Posted Tuesday, October 26, 2010 6:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
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).
Post #1011217
Posted Wednesday, October 27, 2010 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
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
Post #1011472
Posted Wednesday, October 27, 2010 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 9:31 AM
Points: 4, 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
Post #1011588
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse