replace value of

Can someone please enlighten me on a specific way "replace value of" is used?

I'd simply like to know if replace value of can be used to udpate an empty node value. I can't get it to do that, so I'm thinking it can't, but I would certainly like to be corrected if it is possible.

For example, suppose of I have the following in a Typed XML column:

Code Snippet

<Root>

<FirstName></FirstName>

</Root>

When I use replace value of on the above, it does not update it. However, if i have the following:

Code Snippet

<Root>

<FirstName>Scott</FirstName>

<Root>

replace value of will replace the value with whatever I specify.

If this is the way it is supposed to behave, how do I update a NULL value with an actual value?

Thanks in advance...

use the following expression,

Code Snippet

Declare @.XML as XML

Set @.XML='<Root>

<FirstName>Mani</FirstName>

<FirstName>Scott</FirstName>

<FirstName></FirstName>

<FirstName/>

</Root>'

Select

Data.Nodes.query('

<FirstName>

{

if( string(.) = "" )

then

"No Name"

else

string(.)

}

</FirstName>')

From

@.XML.nodes('/Root/FirstName') as Data(Nodes)

|||

The following example works fine for me (actually I create the schema collection first, then run the other statements):

Code Snippet

CREATEXMLSCHEMACOLLECTION dbo.schema2 AS'

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Root">

<xs:complexType>

<xs:sequence>

<xs:element name="FirstName" type="xs:string"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

';

DECLARE @.example xml(dbo.schema2);

SET @.example ='<Root><FirstName></FirstName></Root>';

SELECT @.example;

SET @.example.modify('

replace value of (Root/FirstName)[1]

with "Scott"

');

SELECT @.example;

If I try the same replace statement with an untyped xml variable then it does not work, the error says that the element needs to be typed.

|||

Since you have schema defined as element FirstName onlu occur once under Root, you can get rid the ()[1] part.

Code Snippet

DECLARE @.example xml(document dbo.schema2);

SET @.example ='<Root><FirstName></FirstName></Root>';

SELECT @.example;

SET @.example.modify('

replace value of Root/FirstName

with "Scott"

');

SELECT @.example;