Insert an XML Node into an XML 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 2005 Forums

Transact-SQL (2005)

Insert an XML Node into an XML Column

Author

Topic

rternier
Starting Member

44 Posts

Posted - 2008-09-23 : 17:45:02

I have the following value in an XML cell:

<RBServerUIConfig><ShowRedactedReports /><EnableExporter /></RBServerUIConfig>

I want to insert: <EnableListManagement /> so it should look like:

<RBServerUIConfig><ShowRedactedReports /><EnableListManagement /><EnableExporter /></RBServerUIConfig>

I'm just pounding my head on how to do this. Any help would rock,

thanks.

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-09-23 : 22:24:11

You can use modify method to do this, look into :

pseudo:


...
declare @t table (x xml)
insert into @t
select '<root><test>yak</test></root>'

select * from @t
update @t
set x = x.modify('insert <test>yak again</test> as first into (/root/)[1]')

select * from @t

Nathan Skerl

rternier
Starting Member

44 Posts

Posted - 2008-09-24 : 10:49:34

I tried this,

And i get the error: XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string ?".

----
DECLARE @XML XML
DECLARE @LM VARCHAR(100)

SET @XML = (SELECT xmlConfiguration FROM tblProcessConfigurations WHERE vchConfigurationKey = 'RBServerUIConfig')
SET @LM = '<EnableListManagement />'

SET @XML.modify('insert sql:variable("@LM") as first into /RBServerUIConfig[1]')
SELECT @XML

go

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

rternier
Starting Member

44 Posts

Posted - 2008-09-24 : 11:30:47

This works:

DECLARE @XML XML
DECLARE @LM VARCHAR(100)

SET @XML = (SELECT xmlConfiguration FROM tblProcessConfigurations WHERE vchConfigurationKey = 'RBServerUIConfig')
SET @LM = '<EnableListManagement />'

--SET @XML.modify('insert sql:variable("@LM") as first into /RBServerUIConfig[1]')
SET @XML.modify('insert <EnableListManagement /> as first into /RBServerUIConfig[1]')
SELECT @XML

go

----
Killer ASP.NET ninja coding monkeys do exist!
[url]http://weblogs.asp.net/rternier[/url]

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-09-25 : 19:45:13

Ok, I see now. In 2005 there is some limitation on inserting node of xml into another node of xml. I believe this is resolved in 2008, but this is what I did in 2005 as a workaround.


declare @Tree xml, 
	@Node xml

declare @Return xml

set @Tree = '<root><test>yak</test></root>'
set @Node = '<test>yak2</test>'

set @Return = convert(xml, (convert(nvarchar(max), @Tree) + convert(nvarchar(max), @Node)))
set @Return.modify('insert /*[2] as first into /*[1]')
set @Return.modify('delete /*[2]')

select @Return

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-09-25 : 19:49:20

In 2008:

http://www.sqlserverandxml.com/2008/01/insert-xml-variable-to-another.html

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources