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

Posted on 2006-04-07
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

    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

    Closed, 250 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now