Re: Sql Server 2005 Xml Dml using .modify()

In order to replace the content of an element, the xml must be strongly typed, i.e. must have an xml schema. You can use sql:variable to access a sql variable inside an XQuery expression. Here is an example:

-- schema for your data
CREATE XML SCHEMA COLLECTION A
AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"; elementFormDefault="qualified">
<xs:element name="SearchPreferenceData">
<xs:complexType>
<xs:sequence>
<xs:element ref="DefaultCountyID"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DefaultCountyID" type="xs:string"/>
</xs:schema>'


DECLARE @id VARCHAR(10)
-- sql variable you want to insert
SET @id = 'abc'

declare @DefaultSearchXml xml(A)
set @DefaultSearchXml = '
<SearchPreferenceData>
<DefaultCountyID>fdsf</DefaultCountyID>
</SearchPreferenceData>'

-- patched up xpath expression
-- and sql:variable
set @DefaultSearchXml.modify('
replace value of (/SearchPreferenceData[1]/DefaultCountyID[1])
with sql:variable("@id")
')

-- check to see if it worked
SELECT @DefaultSearchXml


Dan







declare @DefaultSearchXml xml
set @DefaultSearchXml = '
<SearchPreferenceData>
<DefaultCountyID></DefaultCountyID>
</SearchPreferenceData>'
set @DefaultSearchXml.modify('
replace value of
(/SearchPreferenceData/DefaultCountyID[1]/text())[1]
with "abc"
')


.

Loading