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.

Comments

  • Anonymous
    July 18, 2006
    Thanks. Very helpful

  • Anonymous
    October 03, 2006
    i have needed this loads of times so very helpful

  • Anonymous
    November 27, 2006
    But why do we do this? If the type is not supported and there is no real way to bypass that, this is certainly not a solution. What's done here might work, but looking at the suggested code it's using xml parsing and string concat, nothing I would wan't do in code which require efficeny. I get the feeling that what we are trying to do here is not really intended to be done at all. DML is not very friendly towards updating with external data. Instead, you'd expect that the query to run should provide the xml that is to be inserted inline!?

  • Anonymous
    December 01, 2006
    John, I understand the concern regarding performance but this is a workaround, designed to bypass some internal limitations and it has its own drawbacks. Regarding the "friendliness" of DML with external data, sql:column() and sql:variable() were introduced for that very purpose. Unfortunately at this point they cannot handle XML data.

  • Anonymous
    December 04, 2006
    The comment has been removed

  • Anonymous
    December 04, 2006
    The comment has been removed

  • Anonymous
    December 05, 2006
    denisruc It's a no brainer - Microsoft introduces SQL 2005 with XML support, along with a new datatype - xml - and guess what?  DML does not support xml data types???  DUH .... It's like me buying a new car and the car I bought doesn't support tires. Come on, you get us all hyped up for true xml support (finally) but no support for the datatype in stored procedures.... very sloppy.

  • Anonymous
    December 06, 2006
    Thanks for the workaround while we wait for MS to get a full implementation of XML in SQL. This works ok for adding things directly to the root node, but I'm having trouble adding XML nodes at specific places below the root. I've added a @location parm for the function to replace the '/*', but unfortunately I then get the error "The argument 1 of the xml data type method "modify" must be a string literal." Making the statement dynamic doesn't work, since the @x variable is then out of scope. Any suggestions?

  • Anonymous
    December 08, 2006
    Jeff, From what you wrote I believe you tried to parameterize your query by passing a string Variable to the modify method. Unfortunately that is not allowed and you must use a string literal. I also think I understand why you cannot use dynamic SQL but I would probably need a bit more detail to help you. I suggest you use the contact form on this blog to send me an email with a sample of what you're trying to do.  I'll look at it and try to help you. Also you can search the MSDN forums and the newsgroups to see if somebody else has already dealt with the same problem.

  • Anonymous
    December 11, 2006
    "Also you can search the MSDN forums and the newsgroups to see if somebody else has already dealt with the same problem" YOU WON'T FIND IT.  Microsoft really, really, really, really needs to make this a priority.  Our company switched over to SQL 2005 soley for the XML feature.  We're now discovering that there's not a lot we can do inside procs regarding xml datatype - AND THIS IS REALLY STARTING TO HURT US. With all due respect, please get this enhancement submitted quick - we really need to be able to pass parameters of type XML and allow our procs to accept these parameters and insert them into existing xml strings.

  • Anonymous
    December 12, 2006
    Hi Jeff,   Try this: -- create scratch table ahead of time create table XMLScratchPad (xDoc xml); -- define the xml documents declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX) set @xParentDoc = '<abc><def/></abc>' set @xNewNode = '<ghi/>' -- set the dynamic sql set @sQuery = 'UPDATE XMLScratchPad SET xDoc.modify(''insert ' + CAST(@xNodeNew AS NVARCHAR(MAX)) + ' AS LAST INTO (' + @sParentNode + ')[1]'')' -- exec the sql exec(@sQuery) -- get the result select @sParentDoc = xDoc from XMLScratchPad

  • Anonymous
    December 12, 2006
    And what if @xNodeNew is over 8000 characters?

  • Anonymous
    December 12, 2006
    "Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data." -- from BOL If you need more than that, you are SOL until Microsoft fixes this problem.

  • Anonymous
    December 12, 2006
    Actually 2^31-1 is 2 G which is also the limitation for xml types.

  • Anonymous
    December 13, 2006
    In my haste, it seems I left out a few lines, so let's try this again: -- create scratch table ahead of time create table XMLScratchPad (xDoc xml); -- define the xml documents declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX), @sParentNode nvarchar(255) set @xParentDoc = '<abc><def/></abc>' set @xNewNode = '<ghi/>' set @sParentNode = 'abc/def' insert into XMLScratchPad values(@xParentDoc) -- set the dynamic sql set @sQuery = 'UPDATE XMLScratchPad SET xDoc.modify(''insert ' + CAST(@xNewNode AS NVARCHAR(MAX)) + ' as last into (' + @sParentNode + ')[1]'')' -- exec the sql exec(@sQuery) -- get the result select @xParentDoc = xDoc from XMLScratchPad select @xParentDoc

  • Anonymous
    December 13, 2006
    In my haste, it seems I left out a few lines, so let's try this again: -- create scratch table ahead of time create table XMLScratchPad (xDoc xml); -- define the xml documents declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX), @sParentNode nvarchar(255) set @xParentDoc = '<abc><def/></abc>' set @xNewNode = '<ghi/>' set @sParentNode = 'abc/def' insert into XMLScratchPad values(@xParentDoc) -- set the dynamic sql set @sQuery = 'UPDATE XMLScratchPad SET xDoc.modify(''insert ' + CAST(@xNewNode AS NVARCHAR(MAX)) + ' as last into (' + @sParentNode + ')[1]'')' -- exec the sql exec(@sQuery) -- get the result select @xParentDoc = xDoc from XMLScratchPad select @xParentDoc

  • Anonymous
    December 13, 2006
    Hi! How to pass a dynamic argument in xquery? Say like this: declare @RootElement varchar(50) select @RootElement = '/Root/Metadata' Select m.DocumentId, m.MetadataId, ref.value( './@DisplayField', 'varchar(8000)' ) from dbo.MetadataStore m cross apply StringValue.nodes('sql:variable("RootElement")') as T(ref) --Gives Error :XQuery [dbo.MetadataStore.StringValue.nodes()]: A node or set of nodes is required for 'nodes()' Any Idea?

  • Anonymous
    December 13, 2006
    The comment has been removed

  • Anonymous
    December 13, 2006
    Thanks to all for your help. Brian thanks for pointing out about MAX now allowing for over 8000 chars. This frees me up to use Dynamic SQL on the large XML we have. Not the prettiest solution, but as long as it works...

  • Anonymous
    October 11, 2007
    i want update my xml column data   i tried as fallows Update t set x.modify('replace value of (//Action/PXIIP/text())[1]  with "1.2.3.8"') WHERE i between 1487 and 1497 is working declare @ip varchar(100) set @ip='1.2.3.7' select @ip Update t set x.modify('replace value of (//Action/PXIIP/text())[1]  with @ip') WHERE i between 1487 and 1497 is not working . but i want to pass as a parameter

  • Anonymous
    March 15, 2008
    PingBack from http://blogrssblog.info/typed-xml-in-sql-server-2005-inserting-an-xml-instance-into-another/

  • Anonymous
    March 19, 2008
    Has this been resolved (fixed/handled or otherwise enabled) in SQL Server 2008

  • Anonymous
    March 20, 2008
    That would be a yes; http://www.sqlserverandxml.com/2008/01/insert-xml-variable-to-another.html But it doesn't help us lowly 2005 users... :^(