November 17, 2011 at 10:50 am
I have a stored procedure that updates an XML column in a table with passed values. When decimal values are passed as nulls, the update fails. The update statement (in part) is as follows:
update myTable
set xmlColumn.modify('insert <part1...
<myDecimalValue>(sql:variable("@passDecimalVariable"))</myDecimalValue>
....
....
</part1>
as first into (/root)[1]')
where .....
The XML collection defines this element as
<xsd:element name="myDecimalValue" type="decimalDef" nillable="true"/>
<xsd:simpleType name="decimalDef">
<xsd:restriction base="xsd:decimal"/>
</xsd:simpleType>
When the decimal is passed with a valid value there are no issues. But when it is null, the modify() fails. I considered setting the decimal values to an arbitrary value in the proc or changing the xsd type to a string to force this to work, but neither of those approaches are ideal.
Any advice as to how best to allow for the use of null decimal values would be appreciated.
November 17, 2011 at 12:11 pm
What error do you get when it fails?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2011 at 12:23 pm
Unfortunately I can not cut and paste the error, but essentially the error is:
Msg 6926, Level 16, State 1, Procedure..... Line ..
XML Validation: Invalid simple type: ''. Location: /*:part1[1]......./*:myDecimalValue[1]
November 17, 2011 at 1:25 pm
Found this:
An error is returned if the modify() method is called on a null value or results in a null value.
On: http://msdn.microsoft.com/en-us/library/ms187093.aspx
Page title: modify() Method (xml Data Type)
I don't know a workaround off the top of my head. XML doesn't usually play nice with NULLs in my experience.
Have you tried setting a value to an xsi:nil statement instead of directly to the variable value if the variable is null?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2011 at 12:39 pm
I have not tried and I am not familiar with the xsi:nil approach that you noted. Any recommendations where I can find info on implementing this approach?
November 21, 2011 at 6:48 am
Here's a sample:
DECLARE @XML XML = '<values><Names First="GSquared" /></values>' ;
SET @XML.modify('delete /values/Names/@First') ;
SET @XML.modify('insert attribute xsi:nil {"true"} into (/values/Names)[1]') ;
SELECT @XML ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2011 at 11:10 am
Initially I did not grasp how to work your solution into my scenario, but after a bit of trial and error I was able to use to your suggestions to solve the issue of a null (nil) decimal in an XML string. As a matter of completeness, this is the solution to this problem.
The schema collection
<xsd:element name="myDecimalValue" type="decimalDef" nillable="true"/>
<xsd:simpleType name="decimalDef">
<xsd:restriction base="xsd:decimal"/>
</xsd:simpleType>
was correct, no changes needed with that part.
I reworked the stored procedure to address the condition of a null decimal value. There may be other methods to allow for the null decimal, but this works.
Initially attribute the decimal with to a null value:
update myTable
set xmlColumn.modify('insert <part1...
<myDecimalValue xsi:nil="true"/>
....
....
</part1>
as first into (/root)[1]')
where .....
Next, if a non-null value for the decimal variable is passed, replace the value of the decimal element:
if (@myDecimalValue is not null)
update myTable
set xmlColumn.modify('replace value of (/root/...../myDecimalValue)[1]
with sql:variable("@myDecimalValue") cast as myDecimalValue ?')
Again, thank-you for the help.
November 22, 2011 at 11:34 am
Glad I was able to help at least that much.
The XQuery documentation sucks. I've had to learn far to much of it by just poking around and trying things till it worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply