[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-04-07
Medium Priority
Last Modified: 2007-12-19
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?
Question by:thrillingryan

Author Comment

ID: 16548677
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.

Accepted Solution

GranMod earned 0 total points
ID: 16553544
Closed, 250 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question