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 http://www.microsoft.com/info/cpyright.htm.
