Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. Mark
This is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>
|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!
|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>