Wednesday, February 15, 2012

Entering Empty Node Using An Updategram

Hi there,
I was wondering if anyone has found how to add an empty (not NULL) node
to an update. I found if you create a node you can get it to enter an
empty space by setting the default value to ('') on the database so
that when you query the data you have an empty node returned. The
problem I'm having is when I want to "blank" a node so I have something
like:
<before>
<node>Some text</node>
</before>
<after>
<node/>
</after>
which will result in a NULL value. Is there something I can put in the
after node that will result in it producing the same as ('') or
something to that effect?
Thanks,
Gary
Gary,
I'm assuming you are using the XML datatype and want to perform this DML via
the .modify() method.
In that case, you can use the replace value of DML statement. The new value
you want is "empty" or (). Here is an example:
declare @.x xml
set @.x = '<foo>bar</foo>'
set @.x.modify('replace value of /foo[1]/text()[1] with ()')
select @.x
You will most likely need to modify this DML if you are using a typed xml
column.
Regards,
Galex Yen
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>
|||you need to use xsi:nil. Here is the documentation and an example:
http://msdn2.microsoft.com/en-us/library/ms171764.aspx
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<updg:sync mapping-schema='StudentSchema.xml'>
<updg:before/>
<updg:after>
<Student SID="S00004" lname="Elmaci" minitial="" years="2">
<fname xsi:nil="true">
</fname>
</Student>
</updg:after>
</updg:sync>
</ROOT>
"Gary" wrote:

> Hi there,
> I was wondering if anyone has found how to add an empty (not NULL) node
> to an update. I found if you create a node you can get it to enter an
> empty space by setting the default value to ('') on the database so
> that when you query the data you have an empty node returned. The
> problem I'm having is when I want to "blank" a node so I have something
> like:
> <before>
> <node>Some text</node>
> </before>
> <after>
> <node/>
> </after>
> which will result in a NULL value. Is there something I can put in the
> after node that will result in it producing the same as ('') or
> something to that effect?
> Thanks,
> Gary
>

No comments:

Post a Comment