Inserting an XML instance into another even though SQL type ‘xml’ is not supported in XQuery.

Since we shipped SQL Server 2005 I have seen a few users ask if there was a simple way to insert an XML instance into another one. For simplicity’s sake, in the rest of this post I’m going to assume that all my variables contain valid XML documents (a single top level element). The examples below can easily be adapted to deal with fragments (multiple roots)

 

Let’s imagine that we have two XML variables @x1 and @x2 and that we’d like to insert the instance from @x2 into the root element of the instance stored in @x1. We could try something like this

 

DECLARE @x1 XML, @x2 XML

SET @x1 = '<root/>'

SET @x2 = '<a/>'

SET @x1.modify('insert sql:variable("@x2") as first into /root[1]')

go

Unfortunately, the above query returns the following error

XQuery: SQL type 'xml' is not supported in XQuery.

 

If the two instances were stored in the same variable, then we could easily proceed with the DML operation, as shown below

DECLARE @x XML

SET @x = '<root/>

<a/>'

SET @x.modify('insert /*[2] as first into /*[1]')

SET @x.modify('delete /*[2]')

SELECT @x

go

 

The results for the following script are

<root><a /></root>

 

So the question becomes: how do we combine two instances? There are two ways I know of.

 

The first method requires the conversion of both instances to a string type, like nvarchar(MAX) and the use of concatenation.

 

DECLARE @x1 XML, @x2 XML

SET @x1 = '<root/>'

SET @x2 = '<a/>'

DECLARE @x XML

SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))

SELECT @x

go

 

The second method was shown to me recently and involves FOR XML.

 

DECLARE @x1 XML, @x2 XML

SET @x1 = '<root/>'

SET @x2 = '<a/>'

DECLARE @x XML

SELECT @x = (SELECT @x1 "*", @x2 "*" FOR XML PATH(''), TYPE)

SELECT @x

go

 

With both scripts, variable @x ends up containing the value

<root /><a />

 

Now we can write a function that takes both the source and the target instance as parameters and returns the result. We’ll also make sure corner cases are handled gracefully.

 

CREATE FUNCTION f_insert_as_first (@x1 XML, @x2 XML)

RETURNS XML

AS

BEGIN

      -- insert into null value is impossible

      IF (@x1 is null)

            RETURN null

    -- if the value to insert is null or if @x1 contains no element we do nothing

      IF ((@x1.value('count(/*)','int') = 0) OR (@x2 is null))

            RETURN @x1

      -- if there is no element to insert we do nothing

      IF (@x2.value('count(/*)','int') = 0)

            RETURN @x1

      -- if any one of the two instances is not a valid document (more than one root element)

      -- we do nothing

      IF ((@x1.value('count(/*)','int') > 1) OR (@x2.value('count(/*)','int') > 1))

            RETURN @x1

      DECLARE @x XML

      SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))

      SET @x.modify('insert /*[2] as first into /*[1]')

      SET @x.modify('delete /*[2]')

      RETURN @x

END

go

Now we can use the function we just created to run our original scenario

 

DECLARE @x1 XML, @x2 XML

SET @x1 = '<root/>'

SET @x2 = '<a/>'

SET @x1 = dbo.f_insert_as_first(@x1, @x2)

SELECT @x1

The result will be as expected

<root><a /></root>

 

 

To end this post, let’s look at a scenario involving a table.

 

CREATE TABLE myTable (iCol int primary key,

xmlCol XML,

extraCol XML)

go

 

Now let’s imagine that for each row the instance contained in column extraCol should be inserted inside the instance contained in xmlCol.

 

Again, we could try something like this

UPDATE myTable SET xmlCol.modify('insert sql:column("myTable.extraCol") as first into /*[1]')

but we already know that this is going to fail and return the following error

XQuery: SQL type 'xml' is not supported in XQuery.

 

The solution is to reuse our function and run

UPDATE myTable SET xmlCol = dbo.f_insert_as_first(xmlCol, extraCol)

 

You can populate the table with various values and check for yourself that the query performs the expected transformation.

 

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.