• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2139
  • Last Modified:

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

DECLARE @xmlToAdd xml

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?
1 Solution
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

<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.
Closed, 250 points refunded.
The Experts Exchange
Community Support Moderator of all Ages
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now