Sónia Moreira

Integração Contínua & TFS & Scrum

My Links

Blog Stats

Posts - 51
Stories - 0
Comments - 35
Trackbacks - 14

Post Categories

Blogs

Modify xml node text with sql xquery

Para modificar o texto de um nó de um documento xml alojado num campo do tipo xml numa base de dados SQL Server 2005, proceda da seguinte forma:

Update

myTable set [myXmlField].modify('
declare namespace os=http://a9.com/-/spec/opensearch/1.1/;
replace value of (//os:OpenSearchDescription/os:Attribution/text())[1] with xs:string("My Company © 2008 all rights reserved")
')
WHERE myXmlField is not nul

Excerto do documento XML:

<OpenSearchDescription xmlns="http://a9.com/-/spec/opensearch/1.1/">
...
<Attribution>MyCompany @ 2007 all rights reserved</Attribution>
</OpenSearchDescription>

Para validar a vossa alteração:

Select myXmlField.value('declare namespace os="http://a9.com/-/spec/opensearch/1.1/"; (/os:OpenSearchDescription/os:Attribution)[1]', 'nvarchar(50)')
From myTable