Updating Xml node in a xmltype column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Updating Xml node in a xmltype column

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-02-14 : 05:10:57

I have a table with xml datatype column PartnerResponse

In it i have a data
<mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"></mailxml:PartnerApptQueryResponse>

I wants to update the node

xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"

into

xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"

How can i do it from sql?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-02-14 : 05:30:21

Thanks but it could be better if you can help me in exact query

I were trying

UPDATE [PartnerAppointmentQueryResponse]
Set PartnerApptQueryResponseType.modify
('replace value of
(/PartnerApptQueryResponse[@xmlns])[1] with "http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"
'
)
Where ConsigneeApptID = 'fg'

but it is not working and i am getting error

XQuery [PartnerAppointmentQueryResponse.PartnerApptQueryResponseType.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(PartnerApptQueryResponse,xdt:untyped) ?'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-14 : 06:23:24

What is it you are trying to accomplish?
You want to change the namespace?

N 56°04'39.26"
E 12°55'05.63"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-02-14 : 06:44:10

Yes Peso [the geek]

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-14 : 06:57:17

[code]DECLARE @Sample TABLE
(
ConsigneeApptID CHAR(2) NOT NULL,
PartnerResponse XML NOT NULL
)

INSERT @Sample
SELECT 'fg',
'<mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"></mailxml:PartnerApptQueryResponse>'

-- Before
SELECT *
FROM @Sample

-- Do the update
UPDATE @Sample
SET PartnerResponse = REPLACE(CAST(PartnerResponse AS NVARCHAR(MAX)), N'xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml60d/mailxml"', N'xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"')
WHERE ConsigneeApptID = 'fg'

-- After
SELECT *
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-02-14 : 08:00:44

Thanks Peso

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources