Please Help with XQuery.insert (XML DML) in SQL Server 2005 Stored Procedure

I have 2 variables in my stored procedure od XML data type: @xmlResult holds an XML document and @xmlToAdd holds an XML document fragment that I need to add in an explicit location within the data held in @xmlResult.  I fill @xmlResult from a SELECT...FOR XML AUTO.  I fill @xmlToAdd with a different SELECT...FOR XML AUTO query.

DECLARE @xmlResult xml
SET @xmlResult = (SELECT DISTINCT...FOR XML AUTO)

DECLARE @xmlToAdd xml
SET @xmlToAdd = (SELECT...FOR XML AUTO)

DECLARE @textToAdd varchar(MAX)
SELECT @textToAdd = CONVERT(varchar(MAX),@xmlToAdd)

SET @xmlResult.modify('insert sql:variable("@textToAdd") as first into (/report/client/contract/service[@id="18"])[1]')

I think I have the syntax wrong.  I found out that I can't use sql:variable with the XML data type, so as you can see I converted this into the varchar(MAX) variable, @textToAdd.  Using the format above using .modify(), I receive the following error:

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

I also tried putting braces or backets around the sql:variable part like I have seen is some examples online, like: {sql:variable("@textToAdd") } or [sql:variable("@textToAdd") ] in which cases, I receive the following error:

XQuery [modify()]: An expression was expected

Which indicates to me that now it isn't even seing that part of the statement.  What is the correct syntax or solution?
thrillingryanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thrillingryanAuthor Commented:
I figured this out on my own. <sigh>  Sql Server does not support this as of the release of SQL Server 2005.  They are hoping to release it in Service Pack 1...

From a Microsoft engineer:

"For this case, there is no perfect solution unfortunately because we don't support XML variable for sql:variable :).  Even he constructs a node expression like following way the XML fragmentation part will be treated as text node so the result might not be what he want.

DECLARE @xmlDoc xml
SET @xmlDoc = (SELECT 1 a FOR XML RAW('root'), ELEMENTS)
DECLARE @xmlDocFragment nvarchar(max)
SET @xmlDocFragment = (SELECT 1 a FOR XML RAW)

SET @xmlDoc.modify('insert <n>{sql:variable("@xmlDocFragment")}</n> as first into (/root)[1]')

SELECT @xmlDoc

Result:
===============
<root><n>&lt;row a="1"/&gt;</n><a>1</a></root>

We will definitely solve this problem in Katmai."

I guess Katmai is "Service Pack 1", but I read this about Service Pack 1 somewhere else on the web.
GranModCommented:
Closed, 250 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.